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