I have a range with blank rows as separators. I want to shade all the blank rows. Is there a way to do this?
Printable View
I have a range with blank rows as separators. I want to shade all the blank rows. Is there a way to do this?
Something like this:
[VBA]
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
[/VBA]regards,
Simon
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...?
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:
[VBA]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
[/VBA]
Here's another way:
[VBA]
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
[/VBA]Regards,
Simon
[vba]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
[/vba]
Thank you all for the help...All solutions accepted...
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