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 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