PDA

View Full Version : Shade all Blank rows in Range



naruto
04-02-2007, 08:11 PM
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
04-02-2007, 08:17 PM
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
04-02-2007, 08:24 PM
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
04-02-2007, 08:32 PM
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
04-02-2007, 09:46 PM
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
04-03-2007, 12:01 AM
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
04-03-2007, 02:01 AM
Thank you all for the help...All solutions accepted...

lenze
04-03-2007, 12:30 PM
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