Conditional formatting for blank cells in VBA

[Stev087]

I have a report template that colours cells according to their number value higher than a value, lower than a value and in between.
The in-between value will normally include 0.
In my report I have a blank row between groups of data. At present this is being colored green (same as 0 values).
I want blanks to have no colour to them but can’t they the coding right.
Below is my coding, I just want something to add below this to ensure that blanks on column 8 are not colored the same as zeros.

ActiveSheet.ListObjects("Table").ListColumns(8).DataBodyRange.Select

With Selection.FormatConditions.Add(xlCellValue, xlGreater, "=$C$22")
    .Interior.Color = RGB(255, 199, 206)
    .Font.Color = RGB(156, 0, 6)
End With

With Selection.FormatConditions.Add(xlCellValue, xlLess, "=$C$23")
    .Interior.Color = RGB(200, 200, 255)
    .Font.Color = RGB(0, 0, 120)
End With

With Selection.FormatConditions.Add(xlExpression, xlBetween, "=$c$23", "=$C$22")
    .Interior.Color = RGB(198, 239, 206)
    .Font.Color = RGB(0, 97, 0)
End With

 

[aRandomHelper]

Try changing your 3rd conditional formatting to this:

Dim a
a = Selection.Resize(1, 1).Address(0, 1)
With Selection.FormatConditions.Add(xlExpression, Formula1:="=AND(" & a & ">=$C$23," & a & "<=$C$22," & a & "<>0)")
    .Interior.Color = RGB(198, 239, 206)
    .Font.Color = RGB(0, 97, 0)
End With

Not sure if I got your upper and lower limits right, if not, just swap C22 and C23 or the > and < symbols

 

[Stev087]

Thanks RandomHelper. That has got me close, but now 0 values are also not coloured. I want these to be green

 

[aRandomHelper]

My bad, no idea why I had 0 in mind when what you want to exclude are blanks.

Change to this

With Selection.FormatConditions.Add(xlExpression, Formula1:="=AND(" & a & ">=$C$23," & a & "<=$C$22," & a & "<>"""")")

 

[Stev087]

Thanks Random... works perfect now!