Alternating fill colour

[Damo10]

Hi,

I have a report that has a variable number of rows depending on when it is run, what I would like to do is have an alternating fill colour for the cells in columns D:F starting from row 9 for all rows that have a value in column D, can anyone suggest the best way to do this?
I would prefer a VBA solution rather than a conditional formatting due to the number of rows and sheets.

Regards Damian

 

[SydneyGeek]

Here's a starting point.

Sub Banding()
    Dim c As Range
    Dim rwLast As Long
    
    rwLast = Range("D" & Rows.Count).End(xlUp).Row
    For Each c In Range("D9:D" & rwLast)
        If c.Row Mod 2 = 1 Then
            c.Resize(1, 3).Interior.ColorIndex = xlNone
        Else
            c.Resize(1, 3).Interior.ColorIndex = 36
        End If
    Next c
End Sub

To get the highlight colour you want, run the macro recorder while you select something from the colour picker. Then substitute the ColorIndex=36 with whatever colour you get (2007 and 2010 generally give you a TintAndShade along with a ThemeColor; in this case you need a line for each)...

... or something like

      With c.Resize(1,3).Interior
           .ThemeColor=xxx
           .TintAndShade=yyy
      End With

Denis

 

[JackDanIce]

Do you mean for each cell in D9 onwards that has a value to colur D9:F9 1 colour and then next one another colour, e.g.:

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #00ff00; TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #00ff00"></TD><TD style="BACKGROUND-COLOR: #00ff00"></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">2</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #ff0000"></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #00ff00; TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #00ff00"></TD><TD style="BACKGROUND-COLOR: #00ff00"></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">2</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #ff0000"></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #00ff00; TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #00ff00"></TD><TD style="BACKGROUND-COLOR: #00ff00"></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">2</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #ff0000"></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
If so, try:

Sub ColourMeBins ()
 
Dim i As Long
Dim cChange As Boolean
 
Application.ScreenUpdating = False
 
For i = 9 To Range("D" & Rows.Count).End(xlUp).row
  If Len(Range("D" & i)) > 0 Then
    If cChange Then
      Range("D" & i & ":F" & i).Interior.ColorIndex = 3
    Else
      Range("D" & i & ":F" & i).Interior.ColorIndex = 4
    End If
    cChange = Not cChange
  End If
Next i
 
Application.ScreenUpdating = True
 
End Sub

 

[Damo10]

Thankyou both for your solutions, they both do what I require

Regards.