Posts Tagged ‘VBA’

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