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

 

[jasonb75]

Assuming that the cells are empty you could use something like this, where the variable rng refers to the intersection of the current row and the desired column.

On Error Resume Next

With Rng
    .Interior.ColorIndex = 0
    With .SpecialCells(xlBlanks)
        Debug.Print .Count
        If .Count < 5 Then .Interior.ColorIndex = 31
    End With
End With

On Error GoTo 0

 

[Osvaldo Palmeiro]

Try:

Sub HighlightCells()
 Dim c As Range
  For Each c In Range("Y2:Y" & Cells(Rows.Count, "Y").End(3).Row)
   If Application.CountA(c.Offset(, 1).Resize(, 5)) Mod 5 = 0 Then
    c.Offset(, 1).Resize(, 5).Interior.Color = xlNone
   Else: c.Offset(, 1).Resize(, 5).SpecialCells(4).Interior.ColorIndex = 31
   End If
  Next c
End Sub

 

[Jak]

Hi jasonb75 and OsvaldoPalmerio

Thanks for the quick replys. My problem is resolved. Thank you very much.

 

[Peter_SSs]

Glad to hear your problem is resolved.
If it was resolved by one of the posts in the thread then you can mark that post as the solution

If it was resolved elsewhere and you would like to post the solution here then it is perfectly fine to mark that post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution. As you will see I have the removed the 'Mark as solution' from post #4.

 

[Jak]

Glad to hear your problem is resolved.
If it was resolved by one of the posts in the thread then you can mark that post as the solution

If it was resolved elsewhere and you would like to post the solution here then it is perfectly fine to mark that post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution. As you will see I have the removed the 'Mark as solution' from post #4.

Hi Peter, no problem. I ticked the wrong post. Updated now.

 

[Peter_SSs]

I ticked the wrong post. Updated now.

Easy enough to do. Thanks for fixing it.

 

[Jak]

Hi, follow up question. Condition 3 has changed slightly in that it needs to colour all blank cells.

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
Change:
3. If the cells in ie row 2 ( Z2, AA2, AB2, AC2 and AD2 ) Have Some blanks then All Blank in the usedrange should have a cell.Interior.ColorIndex = 31

Any help with an amendment would be great.

 

[Osvaldo Palmeiro]

Hi. Try:

Sub HighlightBlankCells()
 Range("Z2:AD" & Cells(Rows.Count, "Y").End(3).Row).Interior.Color = xlNone
 On Error Resume Next
 Range("Z2:AD" & Cells(Rows.Count, "Y").End(3).Row).SpecialCells(4).Interior.ColorIndex = 31
End Sub

 

[Jak]

Hi Osvaldo

Thanks for the follow up. When all of the cells in Z2:AD are blank, it is colouring the cells. Under this condition, they should be xlNone.

If every cell is blank in Z2:AD, Cells should be xlNone
If every cell in Z2:AD has a value, Cells should be xlNone
If the range Z2:AD contains a mix of rows with all blanks, all values and some values, then all the blank cells in Z2:AD will be highlighted.