highlight blank cells if condition is met

[Jak]

Hi all

I an looking for help with VBA as part of a larger macro to loop through each row in a range and based on condition 3, highlight blank cells

The columns to check are "Z : AD" starting at row 2 to the end of the used range

Conditions:
1. If the cells in ie row 2 ( Z2, AA2, AB2, AC2 and AD2 ) are All blank, then the cells.Interior.ColorIndex = 0
2. If the cells in ie row 2 ( Z2, AA2, AB2, AC2 and AD2 ) All Have Values, then the cells.Interior.ColorIndex = 0
3. If the cells in ie row 2 ( Z2, AA2, AB2, AC2 and AD2 ) Have Some blanks then the cells.Interior.ColorIndex = 31

Thanks in advance.

Jak

 

[Osvaldo Palmeiro]

Please, try this instead. Hope this time I've followed your criteria . Sorry for that.

Sub HighlightBlankCellsV2()
 Dim LR As Long, rng As Range
  LR = Cells(Rows.Count, "Y").End(3).Row
  Set rng = Range("Z2:AD" & LR)
  rng.Interior.Color = xlNone
  If Application.CountA(rng) > 0 And Application.CountA(rng) < rng.Cells.Count Then
   rng.SpecialCells(4).Interior.ColorIndex = 31
  End If
End Sub

 

[Jak]

Hi Osvaldo, that's spot on. The third condition change is working. Thanks for the follow up support.