CODEWITHSUNDEEP
excelbackground-colorvba
Jolly Sapper
Jolly Sapper

Reputation: 45

Background formatting pairs of rows in a spreadsheet

I was needing a way to format alternating pairs of rows in a worksheet with the same background color. When looking around I found the below link:

Excel VBA: Alternate Row Colors in Range

My problem is similar except that instead of wanting every other row auto filled with a background color I need adjacent pairs of rows colored. For instance, from my start point at row 4 columns A:T would be filled, row 5 columns A:T would have the same background color, rows 6 and 7 columns A:T would be without background color, rows 8 and 9 would share the background color as rows 4 and 5, repeated until the end of the spreadsheet.

I've made an attempt at using conditional formatting for this purpose but 1) I had not been able to get the background alternating for every pair of rows from my start point and 2)it overrides the few special cases that have a different background color 3) conditional formatting does not allow me to manually format any rows that the conditional formatting function formatted.

Many thanks to the commenters for their suggestions (which put me on the right track) but due to the limitations of Conditional Formatting I cobbled together the following Macro that allows the background to be formatted to my needs without eliminating the ability to correct for special cases. The code is heavily commented to help other newbies to understand what the code means and what to modify to change the behavior of the Macro.

Sub AlternateRowColors()

''PURPOSE:  To format the background color of alternating pairs of rows in a designated range of cells with values.
''          A correction to account for a possible empty row at the end of the range not having a value failing to follow the
''          desired pattern is included.
''          Example:            Column A
''                        Row 1:  Green
''                        Row 2:  Green
''                        Row 3:  No Background Color
''                        Row 3:  No Background Color
''          Repeating this pattern until the end of the used cells of a worksheet.

Dim lastUsedRow As Long                     ''Variable to hold the last row number.

lastUsedRow = Range("A200").End(xlUp).Row   ''This checks backwards from cell A200 to A1 for the last row with an entry in Column A
                                            ''and saves the row number in variable lastUsedRow. Modify this as needed for each worksheet.

If lastUsedRow Mod 2 = 0 Then               ''This block of code corrects for the second row of an entry not being highlighted at the
    lastUsedRow = lastUsedRow + 1               ''end of the worksheet if the first cell in the following row is blank.
        Else
End If

For Each cell In Range("A4:T" & lastUsedRow)    ''Sets the range of rows and columns that the background color formatting is to affect.
    If cell.Row Mod 4 = 0 Then                  ''Highlight row if the row number has a divided remainder of zero
        cell.Interior.ColorIndex = 35           ''Sets background color by using .ColorIndex instead of RGB.  Change to suit your need.
            Else
        If cell.Row Mod 4 = 1 Then              ''Highlight row if the row number has a divided remainder of 1
            cell.Interior.ColorIndex = 35       ''Sets background color by using .ColorIndex instead of RGB.  Change to suit your need.
        End If
    End If
Next cell

End Sub
''ADDITIONAL NOTES:
''NONE

Upvotes: 0

Views: 1454

Answers (2)

John Coleman
John Coleman

Reputation: 51998

In conditional formatting use the formula

=Mod(Row(),4) < 2

in the cells you want the rules to apply to

Upvotes: 1

Clif
Clif

Reputation: 439

Try these formulas in conditional formatting > using a formula to determine which cell's to format:

  • =AND(ROW()>3,MOD(ROW(),4)=1)

    and

  • =MOD(ROW(),4)=0

both would apply to $A:$T

Put your rules for specially formatted cells after these general rules.

Hope this helps.

Upvotes: 1

Related Questions