Row Grouping

[Justin Philips]

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

[Gary Keramidas]

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" <[email protected]> wrote in message
news:[email protected]...
>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
>

[DS]

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
>
>

[Justin Philips]

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?

[Toppers]

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?
>
>