Alternating Changing Color of Cells (a Range) across a Spreadsheet

[jeff]

Hi again

I want to be able to change the color of a range of cells across a
spreadsheet alternating the colors ie

Range(A1,H1) - Pale Blue then Range(I1,N1) Pale yellow then Range(O1,S1)
Pale Blue and so on....

Any advice on this simple idea? I was hoping to be able to do something like
Range(A1,H1).BackColor = RGB(192, 255, 255)
but I think that was wishfull thinking...

cheers
Jeff

 

[Carim]

Hi Jeff,

Using conditional formatting , Formula Is :
=MOD(ROW(),2)=0
and pick your color ...

HTH
Carim

 

[Peter T]

Hi Jeff,

Change

Range(A1,H1).BackColor = RGB(192, 255, 255)

to
Range("A1,H1").Interior.Color = RGB(192, 255, 255)

or if you mean cells A1 to H1 ?

Range("A1:H1").Interior.Color = RGB(192, 255, 255)

Note when you apply a RGB color the eventual color will be will be matched
to the closest colour that exists in the palette. In the case of a default
palette your colour will applied as RGB(204, 255, 255), namely colorindex 20
one of the chart colours not shown in the normal dropdown (duplicated with
colorindex 34 in the "upper" part of the palette).

If you particularly want your own RGB customize one of the palette colours,
eg
Activeworkbook.colors(35) = RGB(192, 255, 255)

If your code always works with a similar palette, whether or not customized,
you could do -
Range("A1,H1").Interior.colorindex = 34

I don't follow what you mean by "and so on...."
There's no consistency in your column numbers accross like say every 5 cells
or every 5th cell, or do you mean repeat down rows and each row the same.
Perhaps you mean -

Range("A:A,H:H"). or Range("A:H").

Or if you want to format alternate rows Carim's suggestion works well.

Regards,
Peter T

 

[news.microsoft.com]

Peter

You have hit the nail on the head!! I was inaccurate with my column
examples, so i may have confused the issue ... but essentially its the
Interior.Color which I was looking for...

thanks again..
jeff