I’m a big fan of conditional formatting in Excel. The visual display of colour helps me to quickly analyse data in a way that numbers alone never could. However, recently a colleague sent me a spreadsheet that didn’t have any conditional formatting in it. Instead, she’d actually manually changed the fill colours of the cells and wanted me to write a formula to count how many lines were red and how many were green.

Unfortunately, it is not quite as simple as it sounds. As far as I am aware, there is no formula in excel which will count the number of cells that are formatted in a specific way. So my first question to my colleague was “What determines whether a cell is red or green?” I thought that I could write a formula to count for example all of the values above 25 or below 10. Unfortunately her answer did not help. In fact, the red and green lines weren’t based on the values in the cell, but on other data not managed by the sheet. My next question was then “Why don’t you create a column to manage this data and use conditional formatting?” At this point a look of horror came over my colleague’s face. Apparently the colours rarely changed and the sheet she had sent me was only a sample of the data, in fact in reality the data was several thousand lines long.

So, back to the drawing board so to speak. Having exhausted my knowledge of excel formulae, I then turned to my trusty friend Google search. Google however merely confirmed my suspicions. At this point I turned to vba and it turned out to be relatively easy.

- First I needed to copy the formatting of the coloured column to a new blank one. As an example in the code below, I’ve used column A to column B
- Then I needed the vba to check the cells in the new column B to see if they were red and if so put a cell value of 1
- Repeat the same operation, but check the cells in column B to see if they were green and put a cell value of 2
- Then remove all formatting from both columns
- Add conditional formatting to show a red or green fill in columns A and B based on the new values in column B
- Add a formula to count the number of cells in column B with a value of 1
- Add a formula to count the number of cells in column B with a value of 2

And voila! Now all my colleague needs to do is update the values in column B to change the colours and she can read at a glance how many red and green lines she has.

BEFORE: AFTER: CONDITIONAL FORMATTING:

Sub ColourCount()

‘ default red = RGB(255,0,0)

‘ default green = RGB(0,255,0)

‘ Add a new column B to the data and copy the formatting from column A to column B

Columns(“A:A”).Select

Selection.Copy

Columns(“B:B”).PasteSpecial Paste:=xlFormats

Application.CutCopyMode = False

‘ add a value of 1 if the cells in column B are red

Set rngColour = Range(“B1:B20000”)

For Each cell In rngColour

If cell.Interior.Color = RGB(255, 0, 0) Then cell.Value = 1

Next

‘ add a value of 2 if the cells in column B are green

For Each cell In rngColour

If cell.Interior.Color = RGB(0, 255, 0) Then cell.Value = 2

Next

‘ remove all formatting from columns A and B

Set rngNoColour = Range(“A1:B20000”)

rngNoColour.Interior.ColorIndex = xlNone

‘ add the conditional formatting based on the values in column C

lRed = 1

lGreen = 2

With rngNoColour.FormatConditions.Add(Type:=xlExpression, Formula1:=”=$B1=” & lGreen)

.Interior.Color = RGB(0,255,0)

End With

With rngNoColour.FormatConditions.Add(Type:=xlExpression, Formula1:=”=$B1″ & lRed)

.Interior.Color = RGB(255,0,0)

End With

‘ add countif formulas to count the number of green and red values

Range(“C1”).Select

ActiveCell.FormulaR1C1 = “Red”

Range(“C2”).Select

ActiveCell.FormulaR1C1 = “Green”

Range(“D1”).Select

ActiveCell.FormulaR1C1 = “=COUNTIF(RC[-2]:R[200000]C[-2], 1)”

Range(“D2”).Select

ActiveCell.FormulaR1C1 = “=COUNTIF(R[-1]C[-2]:R[200000]C[-2],2)”

End Sub

It seems useful. I will try it.