VBA and FORMULA: Count Red Bold Text from Conditional Formatting

[chrono2483]

Hello Everyone!

I've tried to find the answer to this online and have tried many VBA/formula to achieve what I need to do, with no success.

I have a set of data that i've applied conditional formatting. I've used the Top 20% and Bottom 20% to highlight the applicable cells as RED BOLD text. Outside of the conditional formatting, I already have these cells colored. So I am looking to only apply a formula that counts the # of cells that have the RED BOLD text applied by conditional formatting.

Sample:

	A	B	C	D
1	200	20%	50	100
2	49	15%	49	101
3	25	0.1%	88	20
4	149	0.0%	100	12

<tbody>
</tbody>

Row 1 = 1
Row 2 = 1
Row 3 = 2
Row 4 = 0


I hope this makes sense. Using a countif based on the same conditions as the conditional formatting is not an options. I am looking for a VBA code and/or another formula that will achieve the same outcome.

Thank you.

 

[lrobbo314]

This should do it.

Function BoldRed(r As Range) As Long
Dim Cnt As Long
Dim cel As Range
For Each cel In r
    If cel.Font.Color = vbRed And cel.Font.Bold = True Then Cnt = Cnt + 1
Next cel
BoldRed = Cnt
End Function

Then your formula would take the entire range that you're working with as its only argument, e.g. =BoldRed(A1:D4)

 

[Rick Rothstein]

Here is a macro that you can use (it puts the output two columns over from the last data column)...

[table="width: 500"]
[tr]
	[td]Sub CFRedBoldCounts()
  Dim R As Long, C As Long, StartRow As Long, StartCol As Long, LastRow As Long, LastCol As Long, RowCount As Long
  StartRow = 1
  StartCol = 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  For R = StartRow To LastRow
    RowCount = 0
    For C = StartCol To LastCol
      If Cells(R, C).DisplayFormat.Font.Bold And Cells(R, C).DisplayFormat.Font.Color = vbRed Then RowCount = RowCount + 1
    Next
    Cells(R, LastCol + 2).Value = RowCount
  Next
End Sub[/td]
[/tr]
[/table]

 

[chrono2483]

This should do it.

Function BoldRed(r As Range) As Long
Dim Cnt As Long
Dim cel As Range
For Each cel In r
    If cel.Font.Color = vbRed And cel.Font.Bold = True Then Cnt = Cnt + 1
Next cel
BoldRed = Cnt
End Function

Then your formula would take the entire range that you're working with as its only argument, e.g. =BoldRed(A1:D4)

Thank you lrobbo314 - your approach works when manually making text BOLD and RED. However it does not seem to pick up those that were formatting through conditional formatting. Could I be doing something wrong?

 

[chrono2483]

Here is a macro that you can use (it puts the output two columns over from the last data column)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CFRedBoldCounts()
  Dim R As Long, C As Long, StartRow As Long, StartCol As Long, LastRow As Long, LastCol As Long, RowCount As Long
  StartRow = 1
  StartCol = 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  For R = StartRow To LastRow
    RowCount = 0
    For C = StartCol To LastCol
      If Cells(R, C).DisplayFormat.Font.Bold And Cells(R, C).DisplayFormat.Font.Color = vbRed Then RowCount = RowCount + 1
    Next
    Cells(R, LastCol + 2).Value = RowCount
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Amazing! Thank you Rick - it worked!