I spent a VERY long time today looking up a method to alternate row colors within a specified range. There really isn't a lot out there and to be honest what I found just looked over-complicated. So, I decided to stop acting like a shameless 'script-kiddy' and put the below sample together: Sub AlternateRowColors() Dim lastRow as Long lastRow = Range('A1').End(xlDown).Row For Each Cell In Range('A1:A' & lastRow) 'change range accordingly If Cell.Row Mod 2 = 1 Then 'highlights row 2,4,6 etc = 0 highlights 1,3,5 Cell.Interior.ColorIndex = 15 'color to preference Else Cell.Interior.ColorIndex = xlNone 'color to preference or remove End If Next Cell End Sub Now I know that works, but I was wondering if there's a simpler method? If so, please do tell because I'm very eager to learn simplification as I have a tendency to write verbose code at present. If not, then may this entry find it's way to page 1 of Google for it's search term(s), because it took me absolutely ages to find anything even remotely useful.
Highlight every other row using table styles (Excel banded rows) The fastest and easiest way to apply row shading in Excel is by using predefined Excel table styles. Along with other benefits of tables such as automatic filtering, color banding is applied to rows by default. All you need to do is convert a range of data to table. This example shows you how to use conditional formatting to shade alternate rows. Shading every other row in a range makes it easier to read your data. Select a range. On the Home tab, in the Styles group, click Conditional Formatting. Click New Rule. Select 'Use a formula to determine.
Comments left for script-kiddies' benefit. The following lines of code may be removed if your data contains no pre-exisiting colors: Else Cell.Interior.ColorIndex = xlNone. I need to do this frequently and like to be able to easily modify the colors I'm using for the banding. '--- Alternate Row color, only non-hidden rows count Sub Test() Dim iNumOfRows As Integer, iStartFromRow As Integer, iCount As Integer iNumOfRows = Range('D61').End(xlDown).Row '--- counts Rows down starting from D61 For iStartFromRow = 61 To iNumOfRows If Rows(iStartFromRow).Hidden = False Then '--- only non-hidden rows matter iCount = iCount + 1 If iCount - 2 * Int(iCount / 2) = 0 Then Rows(iStartFromRow).Interior.Color = RGB(220, 230, 241) Else Rows(iStartFromRow).Interior.Color = RGB(184, 204, 228) End If End If Next iStartFromRow End Sub.