Forum Discussion
Solved
Alternating row colors
Hello all, I often use =Mod(row),2)-1 to apply alternating row colors, where the first row does not receive the formatting. I also have a simple macro that I use to run that type of conditional for...
Something like this will work:
Sub Test() Dim myVar As Long myVar = ActiveCell.Row If myVar Mod 2 = 1 Then MsgBox "I'm an odd number!" Else MsgBox "I'm an even number" End If End Sub
Matt,
Thank you for responding. I did think of that approach but probably misunderstood the logic as I kept adding the -1 to the code. It seems it does not need that. Here is what I came up but suggestions to simplify would be appreciated.
Sub Banding()
Dim myVar As Long
myVar = ActiveCell.Row
If myVar Mod 2 = 1 Then
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False
End If
If myVar Mod 2 = 0 Then
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False
End If
End Sub
Thank you for responding. I did think of that approach but probably misunderstood the logic as I kept adding the -1 to the code. It seems it does not need that. Here is what I came up but suggestions to simplify would be appreciated.
Sub Banding()
Dim myVar As Long
myVar = ActiveCell.Row
If myVar Mod 2 = 1 Then
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False
End If
If myVar Mod 2 = 0 Then
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False
End If
End Sub
I tried the macro and this code seems to work for me:
Sub Banding()
'Even Rows Start with banding
'Odd Rows don't start with banding
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)-1"
With Selection.FormatConditions(1).Interior
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.599963377788629
End With
End Sub
Matt, you can just make that TintandShade 0.6. The Excel macro recorder tries to approximate the number, but never gets it right, and you'll get a giant number. But it's 60%, or 0.6, in this case.
Thanks for the tip. Always wondered about those odd numbers it comes up with lol
I didn't even release there was an ISEVEN() or ISODD() function... Thanks for the insight. I've always just used Mod()
