Alternating row shading macro

[Answer_the_question]

I am trying to create a macro to alternate row colours in a selected range. Currently using conditional formatting and this is what the code looks like right now

Sub Row_Shadding()
'
' Row_Shadding Macro
'


'
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(SUBTOTAL(103,$E$5:$E5),2)=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -4.99893185216834E-02
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

I use the subtotal function so that the conditional formatting will still work when I group rows, but I dont think this will work if i have a column in the counta range that has a blank cell.

Anyways the really issue is that I can't get the subtotal function to use the top left cell of my selection in the formula (currently have to manually change it to the correct cell after running the macro)

The conditional formatting also appears to only work on the number of rows I used during the initial recording (ie if when I recorded the macro there were 8 rows in the test table, when i apply the macro to a new table it only works on the first 8 rows even if there are 20 rows)

Any help would be greatly appreciated!

Thank you

Less experienced with VBA so the mroe straightforward the better

 

[JoeMo]

This will shade visible alternate rows in a range selection that may or may not include one or more hidden rows (see example below). Be sure to select the visible rows before running the macro.

Sub ColorBandAlternateRows()
'Select a range then run this procedure
'Color bands both filtered and unfiltered data
Dim R As Range, Ar As Range, B As Boolean, i As Long, j As Long
Application.ScreenUpdating = False
Selection.Interior.Color = xlNone
On Error Resume Next
Set R = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If R Is Nothing Then Exit Sub
For j = 1 To R.Areas.Count
    For i = 1 To R.Areas(j).Rows.Count
        If Not B Then
            R.Areas(j).Rows(i).Interior.Color = RGB(200, 200, 200)
            B = True
        Else
            B = False
        End If
    Next i
Next j
Application.ScreenUpdating = True
End Sub

Example visible cells in a filtered range after running the macro.
Stock

*	H	I	J	K
hdr1	hdr2	hdr3	hdr4

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #c0c0c0, align: right"]1[/TD]
[TD="bgcolor: #c0c0c0, align: right"]10[/TD]
[TD="bgcolor: #c0c0c0, align: right"]1[/TD]
[TD="bgcolor: #c0c0c0, align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #c0c0c0, align: right"]7[/TD]
[TD="bgcolor: #c0c0c0, align: right"]1[/TD]
[TD="bgcolor: #c0c0c0, align: right"]2[/TD]
[TD="bgcolor: #c0c0c0, align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="bgcolor: #c0c0c0, align: right"]9[/TD]
[TD="bgcolor: #c0c0c0, align: right"]10[/TD]
[TD="bgcolor: #c0c0c0, align: right"]6[/TD]
[TD="bgcolor: #c0c0c0, align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Answer_the_question]

Joe,

Thank you so much for your help.

However this macro does not continue to alternate row colours after grouping cells. Any thoughts on how I would go about doing that?

I'm not sure how to post a screen shot, but what happens is that if i run the macro and then hide row 14 for instance, rows 13 and 15 will be the same colour when 14 is grouped

Thanks again!

 

[JoeMo]

Joe,

Thank you so much for your help.

However this macro does not continue to alternate row colours after grouping cells. Any thoughts on how I would go about doing that?

I'm not sure how to post a screen shot, but what happens is that if i run the macro and then hide row 14 for instance, rows 13 and 15 will be the same colour when 14 is grouped

Thanks again!

Run it after you group/hide cells. Then again after you ungroup/unhide. I assign the macro to an icon on my Quick-access toolbar (QAT) so it's a simple click after I select the visible range I want to shade.