alternating colours

ozPATT · October 5th, 2005, 08:42 AM

Hi,

I am producing quite a lengthy excel report. To aid in pleasant viewing, I would like to alternate background-colorss for the rows.

could someone help me out with the syntax for working out if a number is a whole number or not? i want to do it so that if the row number divided by 2 is a whole number, use one colour, otherwise use another.

secondly, this is a basic question - i am only a newbie at vba... how do I use RGB colours for a row? Is that possible? or do you have to stick with excels choice?

Visit my site: http://www.drybonesuk.com

mjppaba · October 5th, 2005, 10:10 AM

Sub mdl_AlternateLineColours()
Dim myLastRow As Integer
Dim myRow As Integer

Range("A1").Select
myLastRow = Range("a65000").End(xlUp).Row
myRow = 0

Do Until myRow = myLastRow
    myRow = myRow + 1
    If Int(myRow / 2) = myRow / 2 Then
         ActiveCell.Offset(myRow - 1, 0).EntireRow.Interior.ColorIndex = 6
    Else
         ActiveCell.Offset(myRow - 1, 0).EntireRow.Interior.ColorIndex = 35
    End If
Loop

End Sub

cheers

Matt

ozPATT · October 5th, 2005, 10:18 AM

wow thanks for that. in the end I had a brainwave after stumbling across the mod function, and as I was counting line by line anyway, I just added the following in the loop:

Code:

If (rowSummary Mod 2) <> 0 Then
        .Range("A" & rowSummary & ":Q" & rowSummary).Interior.ColorIndex = 19
        .Range("A" & rowSummary & ":Q" & rowSummary).Interior.Pattern = xlSolid
End If

thanks for your reply anyway.

Visit my site: http://www.drybonesuk.com

mjppaba · October 5th, 2005, 10:19 AM

ActiveCell.Offset(myRow - 1, 0).EntireRow.Interior.Color = RGB(255, 0, 0) ' Return the value for Red.

see the VB help for all the different values of RGB

cheers

Matt