I have a range with blank rows as separators. I want to shade all the blank rows. Is there a way to do this?
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
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
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]
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
[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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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