Color Range of Cells

[halem2]

Hi folks:

I've been trying to work on some code that would color, let's say 30 consecutive cells on the same row if the firt one is not empty. Something like...

range("A5").select
if activecell.select <> "" then "color the 30 cells on that row to the right of A5"
activecell.offset (0,1).select
and keep testing until cell is "blank"

Any help would be appreciated.

[Dirk Van de moortel]

"halem2" <[email protected]> wrote in message
news:[email protected]...
>
> Hi folks:
>
> I've been trying to work on some code that would color, let's say 30
> consecutive cells on the same row if the firt one is not empty.
> Something like...
>
> range("A5").select
> if activecell.select <> "" then "color the 30 cells on that row to the
> right of A5"
> activecell.offset (0,1).select
> and keep testing until cell is "blank"
>
> Any help would be appreciated.

You don't need code for that.
It can be done with a simple conditional format.
Select the cells you want to give a color depending on cell A5.
Enter conditional format:
"Formula is"
=IF( $A5="", 1, 0 )
and hit the format button to choose a pattern.

Dirk Vdm

[Bob Phillips]

Simpler formula - =$A5=""

--

HTH

RP

"Dirk Van de moortel" <[email protected]> wrote
in message news:[email protected]...
>
> "halem2" <[email protected]> wrote in
message
> news:[email protected]...
> >
> > Hi folks:
> >
> > I've been trying to work on some code that would color, let's say 30
> > consecutive cells on the same row if the firt one is not empty.
> > Something like...
> >
> > range("A5").select
> > if activecell.select <> "" then "color the 30 cells on that row to the
> > right of A5"
> > activecell.offset (0,1).select
> > and keep testing until cell is "blank"
> >
> > Any help would be appreciated.
>
> You don't need code for that.
> It can be done with a simple conditional format.
> Select the cells you want to give a color depending on cell A5.
> Enter conditional format:
> "Formula is"
> =IF( $A5="", 1, 0 )
> and hit the format button to choose a pattern.
>
> Dirk Vdm
>
>

[Dirk Van de moortel]

"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
> Simpler formula - =$A5=""

Yep, but then I would write it as
= ( $A5 = "" )

Dirk Vdm

[halem2]

I think I did not explained what I need correctly. I need to color alternating rows and once I move down to a cell that is empty, stop the code. I need to use code because the spreadsheets we work with have over 4000 rows and I really don't want to apply conditional formatting to 2000 rows.

Besides, I need to include it as part of a macro I give to users. Also the cells can have either letters or numbers.

thanks

[halem2]

I want to thank every one for their help. This is how I solved my problem

Sub ColoringAltRows()
'
Dim r1 As Integer ' counter for cells all the way to 60 on same row
Dim r2 As Integer 'last non blank cell in column A
'
Range("A5").Select
'
Do Until ActiveCell = ""
For r1 = 1 To 60
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
ActiveCell.Offset(0, 1).Select
End With
Next r1
ActiveCell.Offset(2, -60).Select
Loop
End Sub