I need a code that will scan through a sheet and color code rows based
on the first column. So basically all dates that are the same will be
one color. (Oct 25-red,Oct 26-blue...etc..etc)

I've tried doing this with a loop that matches cell Ax with Ax+1 and
for all those that match use a colorindex = y which increases. It does
work but it takes soooo long. I just need something that will read
column 1 and for all the same dates color those rows colorindex y, then
move to the next set of dates. I will have quite a few different dates
which is why i want to use colorindex as it can just increase as the
dates increase.

Thanks for you help!

Justin

i use this in conditional formatting, i have dates starting in cell a3
=MOD(DAY(A3),2)=1

and

=MOD(MONTH(A3),2)=0

just set the formatting you want for each and apply it to the appropriate cells

--


Gary


"Justin Philips" wrote in message
oups.com...
I need a code that will scan through a sheet and color code rows based
on the first column. So basically all dates that are the same will be
one color. (Oct 25-red,Oct 26-blue...etc..etc)

I've tried doing this with a loop that matches cell Ax with Ax+1 and
for all those that match use a colorindex = y which increases. It does
work but it takes soooo long. I just need something that will read
column 1 and for all the same dates color those rows colorindex y, then
move to the next set of dates. I will have quite a few different dates
which is why i want to use colorindex as it can just increase as the
dates increase.

Thanks for you help!

Justin

I've found the easiest loop to use in something like this would be something
along the lines of:

Sub Baseline()

Dim Baseline As Date
Baseline = "01/10/2005"

Range("B4").Select
Do
Do Until Selection = ""
SelCol = Selection - Baseline
With Selection.Interior
.ColorIndex = SelCol
.Pattern = xlSolid
End With
Selection.Offset(1, 0).Select
Exit Do
Loop
Loop Until Selection = ""

End Sub

This assumes the column with the dates you're looking at is "B" and uses
01/10/05 as the "start" date. Note that using colorindex like this will give
you some really awful colors that may make life tricky for you (e.g.
colorindex 1 is black.... which'll make reading it a little awkward!). This
will look at each line individually though, but is quicker than the method
you describe below.

Hope it helps
DS

"Justin Philips" wrote:

I need a code that will scan through a sheet and color code rows based
on the first column. So basically all dates that are the same will be
one color. (Oct 25-red,Oct 26-blue...etc..etc)

I've tried doing this with a loop that matches cell Ax with Ax+1 and
for all those that match use a colorindex = y which increases. It does
work but it takes soooo long. I just need something that will read
column 1 and for all the same dates color those rows colorindex y, then
move to the next set of dates. I will have quite a few different dates
which is why i want to use colorindex as it can just increase as the
dates increase.

Thanks for you help!

Justin

Hey DS,

Thanks! I added a Dim SelCol as single and .ColorIndex = SelCol
+20....this starts the loop at a better color.

What I still need though is to color the entire row....i tired
Range("A2").EntireRow.Selection....but that threw and error.

Any ideas?

For example,

Rows(2).Interior.ColorIndex = 3

"Justin Philips" wrote:

Hey DS,

Thanks! I added a Dim SelCol as single and .ColorIndex = SelCol
+20....this starts the loop at a better color.

What I still need though is to color the entire row....i tired
Range("A2").EntireRow.Selection....but that threw and error.

Any ideas?