Sep 19 2019 02:41 AM
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!
Sep 19 2019 05:29 AM - edited Sep 19 2019 05:30 AM
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
Sep 19 2019 06:14 AM
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!
Sep 19 2019 08:12 AM
No, that function was not supposed to be used on the Worksheet like a regular function.
Aug 27 2022 06:15 AM
Would you clarify this statement more clearly as I am not very familiar with VB and am trying to do something similar.
Thanks!
Aug 27 2022 06:34 AM
The function getCountBasedOnCFFontColor posted by @Subodh_Tiwari_sktneer can't be used in a formula in a cell. This is because it refers to the DisplayFormat property of a range; this property doesn't work if used in a cell formula.
The function can only be used in other VBA code, such as the macro CountCellsWithRedFont in the same post.