CODEWITHSUNDEEP
excelvbaformatting
user3287522
user3287522

Reputation: 55

Colour Formatting every other row

I was able to find two codes on stack overflow, but not get it to give one light grey formatted row, followed by white row, help would be appreciated, tried both codes

Sub Colour(rng As Range, firstColor As Long, secondColor As Long)
    rng.Interior.ColorIndex = xlAutomatic
    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 ColourFormatting()
    Dim rng As Range
    Dim firstColor As Long
    Dim secondColor As Long

    Set rng = Range("A1:E10")
    firstColor = Pattern = xlSolid: PatternColorIndex = xlAutomatic: ThemeColor = xlThemeColorDark1: TintAndShade = -0.149998474074526: PatternTintAndShade = 0
    secondColor = TintAndShade = 0: PatternTintAndShade = 0
'
    Call Colour(rng, firstColor, secondColor)
End Sub

Sub ShadeEveryOtherRow()
    Dim Counter As Integer

    'For every row in the current selection...
    For Counter = 1 To Range("A1:E30").Rows.Count
        'If the row is an odd number (within the selection)...
        If Counter Mod 2 = 1 Then
            'Set the pattern to xlGray16.
            Range("A1:E30").Rows(Counter).Interior.Pattern = xlSolid: PatternColorIndex = xlAutomatic: ThemeColor = xlThemeColorDark1: TintAndShade = -0.149998474074526: PatternTintAndShade = 0

        End If
    Next

End Sub

Upvotes: 0

Views: 141

Answers (1)

Tom Malkin
Tom Malkin

Reputation: 2284

You don't need VBA for this - you can use conditional formatting with a formula:

=MOD(ROW(),2)=1

and set the format to light grey. Do this for the first row and then copy that row across the area as formats (paste special).

Your code however uses the wrong delimiter in the parameters, colon in VBA means new line, so your statement

Range("A1:E30").Rows(Counter).Interior.Pattern = xlSolid: PatternColorIndex = xlAutomatic

Is the same as

Range("A1:E30").Rows(Counter).Interior.Pattern = xlSolid 
PatternColorIndex = xlAutomatic

And I assume VBA is using your latest selection for the second line.

Try:

Range("A1:E30").Rows(Counter).Interior.Pattern = xlSolid, PatternColorIndex = xlAutomatic

etc

Upvotes: 1

Related Questions