Have ISODD(SUMPRODUCT(1/COUNTIF ignore all blank cells

37 Views Asked by At

I have a vba code that alternates column color based on naming from column B in a range from columns A to M. The code works great except for if I clear any cell in column B it will remove the alternated color from that cell and below cells. How do I exclude empty cells in the COUNTIF so nothing changes if I ClearContents from any cell in column B?

After code has run

After a cell has been cleared, example 1

After a cell has been cleared, example 2

I tried looking for the exact syntax ISODD(SUMPRODUCT(1/COUNTIF with a countif empty cell option, but no luck.

Sub Alternate_Column_Color()
Dim r As Range

Set r = Range("A2:M2", Range("A2:M2").End(xlDown))
r.FormatConditions.Add Type:=xlExpression, Formula1:="=ISODD(SUMPRODUCT(1/COUNTIF($B$1:$B2,$B$1:$B2)))"


r.FormatConditions(r.FormatConditions.Count).SetFirstPriority
With r.FormatConditions(1)
    .Interior.PatternColorIndex = xlAutomatic
    .Interior.ColorIndex = 19
    .Font.ColorIndex = 26
End With
r.FormatConditions(1).StopIfTrue = False

Set r = Nothing


End Sub
0

There are 0 best solutions below