Home

How to count by FONT COLOR in excel when font color depends on a conditional formatting rule?

%3CLINGO-SUB%20id%3D%22lingo-sub-862802%22%20slang%3D%22en-US%22%3EHow%20to%20count%20by%20FONT%20COLOR%20in%20excel%20when%20font%20color%20depends%20on%20a%20conditional%20formatting%20rule%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-862802%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%26nbsp%3B%20I%20came%20across%20to%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FHow-to-count-and-sum-quot-Condtional-formatting-quot-cells-by%2Fm-p%2F116110%22%20target%3D%22_self%22%3Ethis%3C%2FA%3E%3C%2FSTRONG%3E%26nbsp%3Bthread%20coz%20i%20was%20looking%20for%20an%20answer%20to%20my%20problem.%26nbsp%3B%20And%20found%20this%20post%20%22%3CSTRONG%3E%3CSPAN%20class%3D%22lia-link-navigation%20lia-link-disabled%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fsum-by-color-when-colors-are-set-by-conditional-formatting%2Fm-p%2F36495%22%20target%3D%22_self%22%3Esum%20by%20color%20when%20colors%20are%20set%20by%20conditional%20formatting%3C%2FA%3E%22%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EHowever%2C%20what%20I%20want%20is%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Eto%20count%20all%20cells%20with%20red%20font%20color%20in%20a%20range%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ebut%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EFONT%20COLOR%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ewas%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Ebased%20on%20the%20conditional%20formatting%3C%2FSTRONG%3E.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Erange%20is%20A1%3AA1000%26nbsp%3B%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ethen%20the%20formatting%20I%20made%20was%20to%20turn%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EDUPLICATE%20ENTRIES%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Einto%20a%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ERED%20FONT%20COLOR%3C%2FSTRONG%3E.%26nbsp%3B%20regardless%20of%20the%20text%20content.%26nbsp%3B%20if%20apple%20and%20banana%20were%20duplicated%20twice.%26nbsp%3B%20the%20result%20should%20give%20me%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ENOT%202%20BUT%204%3C%2FSTRONG%3E.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-862802%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-863051%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20by%20FONT%20COLOR%20in%20excel%20when%20font%20color%20depends%20on%20a%20conditional%20formatting%20rule%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-863051%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F411622%22%20target%3D%22_blank%22%3E%40Pau_Me%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20place%20the%20following%20Function%20on%20a%20Standard%20Module.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EFunction%20getCountBasedOnCFFontColor(Rng%20As%20Range%2C%20CFColor%20As%20Long)%20As%20Long%0A%20%20%20%20Dim%20Cel%20%20%20%20%20As%20Range%0A%20%20%20%20Dim%20cnt%20%20%20%20%20As%20Long%0A%20%20%20%20Dim%20i%20%20%20%20%20%20%20As%20Long%0A%20%20%20%20%0A%20%20%20%20For%20Each%20Cel%20In%20Rng%0A%20%20%20%20%20%20%20%20If%20Cel.DisplayFormat.Font.Color%20%3D%20CFColor%20Then%20cnt%20%3D%20cnt%20%2B%201%0A%20%20%20%20Next%20Cel%0A%20%20%20%20%0A%20%20%20%20getCountBasedOnCFFontColor%20%3D%20cnt%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20then%20you%20may%20call%20this%20Function%20in%20your%20main%20routine%20to%20count%20the%20cells%20with%20a%20specific%20font%20color%20set%20based%20on%20the%20conditional%20formatting%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20CountCellsWithRedFont()%0A%20%20%20%20Dim%20Rng%20As%20Range%0A%20%20%20%20Dim%20cnt%20As%20Long%0A%20%20%20%20Set%20Rng%20%3D%20Range(%22A1%3AA1000%22)%0A%20%20%20%20%0A%20%20%20%20'Calling%20Function%20getCountBasedOnCFFontColor%20to%20count%20the%20cells%20based%20on%20CF%20color%0A%20%20%20%20cnt%20%3D%20getCountBasedOnCFFontColor(Rng%2C%20vbRed)%0A%20%20%20%20%0A%20%20%20%20'OR%0A%20%20%20%20'cnt%20%3D%20getCountBasedOnCFFontColor(Rng%2C%20RGB(255%2C%200%2C%200))%0A%20%20%20%20'OR%0A%20%20%20%20'cnt%20%3D%20getCountBasedOnCFFontColor(Rng%2C%20255)%0A%20%20%20%20%0A%20%20%20%20MsgBox%20%22Cells%20with%20Red%20font%20color%20are%20%22%20%26amp%3B%20cnt%20%26amp%3B%20%22.%22%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-863156%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20by%20FONT%20COLOR%20in%20excel%20when%20font%20color%20depends%20on%20a%20conditional%20formatting%20rule%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-863156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehi%20dear!%26nbsp%3B%20thanks%20for%20taking%20time%20to%20help.%3C%2FP%3E%3CP%3EI%20am%20a%20little%20bit%20lost.%26nbsp%3B%20Please%20enlighten%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(1)%20the%20first%20set%20of%20codes%2C%20this%20is%20VB%20code%20and%20will%20be%20placed%20in%20module%201%2C%20right%3F%3C%2FP%3E%3CP%3E(2)%20the%202nd%20set%20of%20codes%2C%20where%20will%20I%20insert%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20putting%20both%20sets%20of%20codes%20in%20the%20VB-module.%26nbsp%3B%20However%2C%20I%20cant%20figure%20out%20the%20formula%20that%20has%20to%20be%20entered%20in%20excel.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20this...%3C%2FP%3E%3CP%3E%3CSTRONG%3Emore%20functions%20%26gt%3B%20user%20defined%20category%20%26gt%3B%26nbsp%3BgetCountBasedOnCFFontColor%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ethe%20formula%20i%20used...%3C%2FP%3E%3CP%3E%3DgetCountBasedOnCFFontColor(B17%3AB1016%2CB17)%3C%2FP%3E%3CP%3EB17%3AB1016%20is%20the%20range%3C%2FP%3E%3CP%3Ethe%20last%20B17%20is%20the%20first%20cell%20of%20my%20data%20in%20RED%20FONT%20COLOR%20(i%20doubted%20this)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20very%20much%20again!%20looking%20forward%20to%20hear%20an%20update!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-863405%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20by%20FONT%20COLOR%20in%20excel%20when%20font%20color%20depends%20on%20a%20conditional%20formatting%20rule%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-863405%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F411622%22%20target%3D%22_blank%22%3E%40Pau_Me%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%2C%20that%20function%20was%20not%20supposed%20to%20be%20used%20on%20the%20Worksheet%20like%20a%20regular%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Pau_Me
New Contributor

Hi!  I came across to this thread coz i was looking for an answer to my problem.  And found this post "sum by color when colors are set by conditional formatting"

However, what I want is to count all cells with red font color in a range but the FONT COLOR was based on the conditional formatting

 

Example: range is A1:A1000  then the formatting I made was to turn DUPLICATE ENTRIES into a RED FONT COLOR.  regardless of the text content.  if apple and banana were duplicated twice.  the result should give me NOT 2 BUT 4.  

 

Thanks in advance!

3 Replies

@Pau_Me 

You may place the following Function on a Standard Module.

 

Function getCountBasedOnCFFontColor(Rng As Range, CFColor As Long) As Long
    Dim Cel     As Range
    Dim cnt     As Long
    Dim i       As Long
    
    For Each Cel In Rng
        If Cel.DisplayFormat.Font.Color = CFColor Then cnt = cnt + 1
    Next Cel
    
    getCountBasedOnCFFontColor = cnt
End Function

 

 

And then you may call this Function in your main routine to count the cells with a specific font color set based on the conditional formatting like this...

 

Sub CountCellsWithRedFont()
    Dim Rng As Range
    Dim cnt As Long
    Set Rng = Range("A1:A1000")
    
    'Calling Function getCountBasedOnCFFontColor to count the cells based on CF color
    cnt = getCountBasedOnCFFontColor(Rng, vbRed)
    
    'OR
    'cnt = getCountBasedOnCFFontColor(Rng, RGB(255, 0, 0))
    'OR
    'cnt = getCountBasedOnCFFontColor(Rng, 255)
    
    MsgBox "Cells with Red font color are " & cnt & "."
End Sub

 

 

@Subodh_Tiwari_sktneer 

 

hi dear!  thanks for taking time to help.

I am a little bit lost.  Please enlighten me.

 

(1) the first set of codes, this is VB code and will be placed in module 1, right?

(2) the 2nd set of codes, where will I insert this?

 

I tried putting both sets of codes in the VB-module.  However, I cant figure out the formula that has to be entered in excel. 

I tried this...

more functions > user defined category > getCountBasedOnCFFontColor

the formula i used...

=getCountBasedOnCFFontColor(B17:B1016,B17)

B17:B1016 is the range

the last B17 is the first cell of my data in RED FONT COLOR (i doubted this)

 

 

thank you very much again! looking forward to hear an update!

@Pau_Me 

No, that function was not supposed to be used on the Worksheet like a regular function.

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies