Skip to content

Instantly share code, notes, and snippets.

@brazilnut2000
Last active December 16, 2015 02:59
Show Gist options
  • Save brazilnut2000/5366447 to your computer and use it in GitHub Desktop.
Save brazilnut2000/5366447 to your computer and use it in GitHub Desktop.
VBA: Alternate coloring for rows in a range.
' I need to do this frequently and like to be able to easily modify the colors I'm using for the banding.
' The following sub makes it very easy:
Sub GreenBarMe(rng As range, firstColor As Long, secondColor As Long, Optional borderColor As Long)
On Error GoTo AlternateGreenBarMe
Application.ScreenUpdating = False
rng.Interior.ColorIndex = xlNone
If Len(borderColor) > 0 Then
Call ThinHorizontalBorders(borderColor, rng)
End If
rng.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0"
rng.FormatConditions(1).Interior.color = firstColor
rng.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)<>0"
rng.FormatConditions(2).Interior.color = secondColor
Exit Sub
AlternateGreenBarMe:
Call SemicolonGreenBarMe(rng, firstColor, secondColor)
End Sub
Sub ThinHorizontalBorders(color As Long, rng As range)
With rng.Borders(xlInsideHorizontal)
.LineStyle = xlNone
.color = color
.Weight = xlThin
End With
End Sub
Private Sub SemicolonGreenBarMe(rng As range, firstColor As Long, secondColor As Long)
' some languages use a semicolon instead of a comma in the Mod formula
rng.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW();2)=0"
rng.FormatConditions(1).Interior.color = firstColor
rng.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW();2)<>0"
rng.FormatConditions(2).Interior.color = secondColor
End Sub
' Usage:
Sub TestGreenBarFormatting()
Dim rng As Range
Dim firstColor As Long
Dim secondColor As Long
dim borderColor as Long
Set rng = Range("A1:D12")
firstColor = vbGreen
secondColor = vbYellow
borderColor = 12566463
Call GreenBarMe(rng, firstColor, secondColor, borderColor)
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment