Forum Discussion
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 formatting. The user selects the area that needs the formatting and runs the macro. Easy enough. However, I have a report that has multiple areas , each with a different number of rows and columns, that need this alternating format where the first row in the selection does not receive the format, But if the row that the selection begins on is an even row number, the formatting is applied to that first row. Is it possible to have a line of code that identifies the first of the current selection as either odd or even? Right now we have two macros, one with the -1 and the other without that. I would like to be able to use only one macro.
Your help would be appreciated.
Kay
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
8 Replies
You can still use conditional formatting, no need for VBA, just adjust your formula. Something like this...
=MOD(ROW($A2)-ISEVEN(ROW(A$2)),2)
This assumes that your conditional formatting starts from A2 as the top-left cell in the applied range.
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 SubI 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
