Macro to check for 3 blank cells in a row

[Moluccanmom]

Hello,
I'm trying to create a macro that goes through different rows (10-20, 25-35....) and checks columns F, G and H to see if at least one cell contains a value (TRUE or FALSE). If all three cells in a row are empty I would like to set the background color of cells A:C to red. If all rows contain values, I would like to display a message box that says: "Everything is complete."
I would appreciate any help to get started!

 

[Joe4]

If all three cells in a row are empty I would like to set the background color of cells A:C to red

This could be done pretty easily with Conditional Formatting, using a formula like:
=AND(F10="",G10="",H10="")
and choosing the red formatting option.

However, see we need to count this instances anyway and return a message box, it is going to require VBA anyway.
Maybe something like this:

Sub MyCheckMacro()

    Dim myRange As Range
    Dim cell As Range
    Dim myCount As Long
    
    Application.ScreenUpdating = False
    
'   Set range to be first cell in each row you want to check
    Set myRange = Range("F10:F20,F25:F35,F40:F50")
    
'   Loop through range
    For Each cell In myRange
        If (cell = "") And (cell.Offset(0, 1) = "") And (cell.Offset(0, 2) = "") Then
            Range(cell, cell.Offset(0, 2)).Interior.Color = 255
            myCount = myCount + 1
        End If
    Next cell
    
'   Check to see how many highlights were applied
    If myCount = 0 Then
        MsgBox "Everything is complete"
    End If
            
    Application.ScreenUpdating = True
    
End Sub

You can adjust the range to suit your needs.

 

[Moluccanmom]

Thank you for helping me again
I have adjusted my range and it works, but can I change the red background color to show up in column A:C instead of F:H?
I tried to move it, but then it changed all cells from A:H to red.
Thanks again!

 

[Joe4]

Are you still checking F:H for the blanks and then highlighting A:C?
Or are you actually checking and highlighting A:C (and F:H is no longer involved at all)?

 

[Moluccanmom]

ADVERTISEMENT

Correct, I check F:H and would like to highlight A:C.

 

[Joe4]

Change this row:

Range(cell, cell.Offset(0, 2)).Interior.Color = 255

to:

Range(cell.Offset(0,-5), cell.Offset(0, -3)).Interior.Color = 255

The key here is that we are looping through column F (so that is the column value of our "cell" variable).
So to get from column F to column A, we need to move 5 columns to the left.
And to get from column F to column C, we need to move 3 columns to the left.
So we use Offset to get there.

 

[Moluccanmom]

ADVERTISEMENT

That makes sense
I had only changed it to -3 which then highlighted all the cells....
Thank you very much for explaining it too!

 

[Joe4]

You are welcome!

 

[Moluccanmom]

I have attached the macro to a button and realized it would be great, if the next time I click it and every row has entries the red color would be cleared.

Problem is, the rows have originally different colors. Maybe I could copy the color from Column D to clear the red? I know it gets really complicated now

 

[Joe4]

If you want to clear all color from your sheet before running the macro, just put this line at the beginning of your code:

    Cells.Interior.Pattern = xlNone