Shade all Blank rows in Range

[naruto]

I have a range with blank rows as separators. I want to shade all the blank rows. Is there a way to do this?

[Simon Lloyd]

Something like this:

 
Sub Macro1()
    Columns("A:A").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="="
    Rows("1:19").Select
    With Selection.Interior
        .ColorIndex = 15
    End With
    Selection.AutoFilter
    End Sub

regards,
Simon

[naruto]

That didn't quite work..Here's what I have...I insert a row after every group and would like to shade that insert row using vba...?

[lucas]

This might not be the best way to do this...uses the used range and looks in column A to see if there is anything there....if not the row gets highlighted:

Sub test()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim lastrow As Long, r As Long
    lastrow = ActiveSheet.UsedRange.Rows.Count
    For r = lastrow To 2 Step -1
        If UCase(Cells(r, 1).Value) = "" Then
            With Rows(r).Interior
                .ColorIndex = 6
                .Pattern = xlSolid
            End With
        End If
    Next r
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

[Simon Lloyd]

Here's another way:

 
Sub Macro1()
    Dim Rng, MyCell As Range
    Dim lRow As Long
    lRow = ActiveSheet.UsedRange.Rows.Count
    lCol = ActiveSheet.UsedRange.Columns.Count
    Set Rng = Range(Cells(1, 1), Cells(lRow, lCol))
    For Each MyCell In Rng
        If MyCell = "" Then
            MyCell.EntireRow.Interior.ColorIndex = 15
        End If
    Next MyCell
End Sub

Regards,
Simon

[mdmackillop]

Sub MarkRows()
    Dim cel as Range
    With ActiveSheet
        For Each cel In Intersect(.Columns(1), .UsedRange).SpecialCells(xlCellTypeBlanks)
            If Application.WorksheetFunction.CountBlank(cel.EntireRow) = 256 Then
                cel.EntireRow.Interior.ColorIndex = 6
            End If
        Next
    End With
End Sub

[naruto]

Thank you all for the help...All solutions accepted...

[lenze]

Why not use CF
Select the entire sheet with A1 active. In CF, choose "Formula Is" option

=COUNTA(A1:IV1)=0

If you only use columns A:R then change it to

=COUNTA(A1:R1)=0

lenze