This post will demonstrate a simple VBA macro to shade background colors for alternate rows in a specified range. This can come in handy when you need to reset the alternate colors for different files, or if you are looking to test out different combinations and want to cycle through them quickly.
Version of Excel: 2013
OS: Windows 10
Excel VBA Alternate Row Colors – Setup
Let’s start with the following table:
Next, add the following module in the VBA Editor:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Option Explicit Sub ShadeAlternateRows() Dim rng As Range Dim ColorOne As Long Dim ColorTwo As Long Set rng = Range("A1").CurrentRegion Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count) ColorOne = 15 ColorTwo = 20 With rng .Interior.ColorIndex = xlNone .FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0" .FormatConditions(1).Interior.ColorIndex = ColorOne .FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)<>0" .FormatConditions(2).Interior.ColorIndex = ColorTwo End With End Sub |
Run the macro, and here is what your Excel table looks like:
You can set the color variables to any of the following, to get your desired look:
Additional Content
Check out more examples by visiting my Home Page
Here you will find topics covering Qlikview SQL Server Excel VBA