CODEWITHSUNDEEP
excelvba
htd
htd

Reputation: 331

Change background color of every second row

I have a given som (general) range where I would like a function to change the background color of every second row in a visual basic function.

One could of course just loop through it and use the mod function, but I think there has to be something quicker.

Thanks in advance.

Edit: I know about conditional formatting, but it is something I need to use over and over again on Ranges that will differ in size so I would like it to be a function I can just run as part of a sub.

Upvotes: 0

Views: 4031

Answers (3)

Paolo Succo
Paolo Succo

Reputation: 1

please find below a solution that does not use the MOD function

Sub ColourEveryOtherRow()

'###############################
'Macro developed by Paolo Succo
'###############################

    Dim firstCol As Long, lastRow As Long, lastCol As Long
    Dim x As Long, Cnt As Long
    Dim index As Boolean
    Dim dataRng As Range

    'speeding up the code
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    'change the range as convenient
    Set dataRng = ActiveSheet.Range("A2:AX10000")

    'set the range parameters
    lastRow = dataRng.Rows.Count
    firstCol = dataRng.Column
    lastCol = dataRng.Columns.Count
    x = 2

    For Cnt = 2 To lastRow + 1
        index = (Int(x / 2) = x / 2)

        'RBG(242, 242, 242) is grey, change as convenient
        If index = True Then
            Range(Cells(Cnt, firstCol), Cells(Cnt, firstCol + lastCol - 1)).Interior.Color = RGB(242, 242, 242)
        Else
            Range(Cells(Cnt, firstCol), Cells(Cnt, firstCol + lastCol - 1)).Interior.Color = xlColorIndexNone
        End If
        x = x + 1
    Next Cnt

    Application.Calculation = xlCalculationAutomatic

End Sub

Please let me know if this help.

Regards,

Paolo

Upvotes: 0

AnalystCave.com
AnalystCave.com

Reputation: 4974

Use this

Sub ChangeEverySecond()
    Dim r As Range
    Set r = Range("A2:A20")
    Dim tmp As Range, i As Integer

    For Each tmp In r.Cells
        i = i + 1
        If i Mod 2 = 0 Then tmp.Interior.Color = RGB(127, 187, 199)
    Next tmp
End Sub

EDIT 1:

Or conditional formatting with a function:

=IF(MOD(ROW();2)=0;TRUE;FALSE)

Upvotes: 1

Sjoerd
Sjoerd

Reputation: 75588

In Excel, you can use conditional formatting to color every other row.

Upvotes: 0

Related Questions