Highlight table rows different color for every change in word

Posted: Monday,November 8, 2010 in Technology
Tags: , ,

Here’s some code that will need tweaking to fit your needs. Here I assumed the table starts in cell A1, and has no blank rows. You can adjust the values of R1,G1,B1 and R2,G2,B2 to give you the pastel shades you want.

Sub TwoColorBands()
Dim rng As Range

Dim lastCol As Long, lastRw As Long, indx As Long, ct As Long
Dim R1 As Integer, G1 As Integer, B1 As Integer
Dim R2 As Integer, G2 As Integer, B2 As Integer

lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
lastRw = Range("A" & Rows.Count).End(xlUp).Row
R1 = 200
G1 = 50
B1 = 100
R2 = 250
G2 = 75
B2 = 120
indx = 0
Set rng = Range("A1", Cells(lastRw, lastCol))
rng.Rows(1).Interior.Color = RGB(R1, G1, B1)
For Each rw In rng.Rows
    If rw.Cells(1).Value = rw.Cells(1).Offset(1, 0).Value Then
        rw.Offset(1, 0).Interior.Color = rw.Interior.Color
    ElseIf rw.Offset(1, 0).Row <= lastRw Then
        indx = indx + 1
        If indx Mod 2 <> 0 Then
            rw.Offset(1, 0).Interior.Color = RGB(R2, G2, B2)
           rw.Offset(1, 0).Interior.Color = RGB(R1, G1, B1)
        End If
    End If
Next rw
End Sub




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s