I am trying to create a macro that will color code the rows in a spreadsheet.

The days for reference are in column O,P.

For days between 1-30 i need the rows to turn green
For days between 31-60 i need the rows to turn yellow
For days between 61-90 i need the rows to turn orange
For days between 91-700 i need the rows to turn red.

Thanks in advance for your time.

Hi Rachel,

You can use something like:

Rows(<number).Interior.ColorIndex = colorindex

Colorindexes are (in Europe)

''' Color index
'''--------------------------
''' Orange 46
''' Dark yellow 12
''' Red 3
''' Light orange 45
''' Lime 43
''' Aqua 42
''' Pink 7
''' Gold 44
''' Yellow 6
''' Bright green 4
''' Turquoise 8
''' Sky blue 33
''' Gray 25% 15
''' Rose 38
''' Tan 40
''' Light yellow 36
''' Light green 35
''' Light turquoise 34
''' Pale blue 37
''' Lavender 39
''' Blue-Gray 16
''' Teal 5
''' Gray 40% 48
''' Gray 25% 15

Wkr,

JP

"Rachel Costanza" <Rachel wrote in
message ...
I am trying to create a macro that will color code the rows in a
spreadsheet.

The days for reference are in column O,P.

For days between 1-30 i need the rows to turn green
For days between 31-60 i need the rows to turn yellow
For days between 61-90 i need the rows to turn orange
For days between 91-700 i need the rows to turn red.

Thanks in advance for your time.

Rachel
Something like this perhaps. I assumed that the reference numbers are
in Column P. HTH Otto
Sub ColorRows()
Dim rColP As Range, i As Range
Dim TheColor As Long
Set rColP = Range("P2", Range("P" & Rows.Count).End(xlUp))
For Each i In rColP
Select Case i.Value
Case 1 To 30: TheColor = 4
Case 31 To 60: TheColor = 6
Case 61 To 90: TheColor = 46
Case 91 To 700: TheColor = 3
End Select
i.EntireRow.Interior.ColorIndex = TheColor
Next i
End Sub
"Rachel Costanza" <Rachel wrote in
message ...
I am trying to create a macro that will color code the rows in a
spreadsheet.

The days for reference are in column O,P.

For days between 1-30 i need the rows to turn green
For days between 31-60 i need the rows to turn yellow
For days between 61-90 i need the rows to turn orange
For days between 91-700 i need the rows to turn red.

Thanks in advance for your time.

This is great! What is the code to stop the colors from streaming into rows
after column P ?

"Otto Moehrbach" wrote:

Rachel
Something like this perhaps. I assumed that the reference numbers are
in Column P. HTH Otto
Sub ColorRows()
Dim rColP As Range, i As Range
Dim TheColor As Long
Set rColP = Range("P2", Range("P" & Rows.Count).End(xlUp))
For Each i In rColP
Select Case i.Value
Case 1 To 30: TheColor = 4
Case 31 To 60: TheColor = 6
Case 61 To 90: TheColor = 46
Case 91 To 700: TheColor = 3
End Select
i.EntireRow.Interior.ColorIndex = TheColor
Next i
End Sub
"Rachel Costanza" <Rachel wrote in
message ...
I am trying to create a macro that will color code the rows in a
spreadsheet.

The days for reference are in column O,P.

For days between 1-30 i need the rows to turn green
For days between 31-60 i need the rows to turn yellow
For days between 61-90 i need the rows to turn orange
For days between 91-700 i need the rows to turn red.

Thanks in advance for your time.

Also, how do I count the number of rows assigned to each color (red, yellow,
orange, green) i.e 14 Red, 20 Green as of a constant date?

"Otto Moehrbach" wrote:

Rachel
Something like this perhaps. I assumed that the reference numbers are
in Column P. HTH Otto
Sub ColorRows()
Dim rColP As Range, i As Range
Dim TheColor As Long
Set rColP = Range("P2", Range("P" & Rows.Count).End(xlUp))
For Each i In rColP
Select Case i.Value
Case 1 To 30: TheColor = 4
Case 31 To 60: TheColor = 6
Case 61 To 90: TheColor = 46
Case 91 To 700: TheColor = 3
End Select
i.EntireRow.Interior.ColorIndex = TheColor
Next i
End Sub
"Rachel Costanza" <Rachel wrote in
message ...
I am trying to create a macro that will color code the rows in a
spreadsheet.

The days for reference are in column O,P.

For days between 1-30 i need the rows to turn green
For days between 31-60 i need the rows to turn yellow
For days between 61-90 i need the rows to turn orange
For days between 91-700 i need the rows to turn red.

Thanks in advance for your time.

Rachel
Find the line of code:
i.EntireRow.Interior.ColorIndex = TheColor
and replace it with:
Range(Cells(i.Row, 1), Cells(i.Row, 16)).Interior.ColorIndex = TheColor
all in one row.
HTH Otto

"Rachel Costanza" wrote in
message ...
This is great! What is the code to stop the colors from streaming into
rows
after column P ?

"Otto Moehrbach" wrote:

Rachel
Something like this perhaps. I assumed that the reference numbers
are
in Column P. HTH Otto
Sub ColorRows()
Dim rColP As Range, i As Range
Dim TheColor As Long
Set rColP = Range("P2", Range("P" & Rows.Count).End(xlUp))
For Each i In rColP
Select Case i.Value
Case 1 To 30: TheColor = 4
Case 31 To 60: TheColor = 6
Case 61 To 90: TheColor = 46
Case 91 To 700: TheColor = 3
End Select
i.EntireRow.Interior.ColorIndex = TheColor
Next i
End Sub
"Rachel Costanza" <Rachel wrote in
message ...
I am trying to create a macro that will color code the rows in a
spreadsheet.

The days for reference are in column O,P.

For days between 1-30 i need the rows to turn green
For days between 31-60 i need the rows to turn yellow
For days between 61-90 i need the rows to turn orange
For days between 91-700 i need the rows to turn red.

Thanks in advance for your time.

Thanks Otto!

How can I make the color coding start in column D?

"Otto Moehrbach" wrote:

Rachel
Find the line of code:
i.EntireRow.Interior.ColorIndex = TheColor
and replace it with:
Range(Cells(i.Row, 1), Cells(i.Row, 16)).Interior.ColorIndex = TheColor
all in one row.
HTH Otto

"Rachel Costanza" wrote in
message ...
This is great! What is the code to stop the colors from streaming into
rows
after column P ?

"Otto Moehrbach" wrote:

Rachel
Something like this perhaps. I assumed that the reference numbers
are
in Column P. HTH Otto
Sub ColorRows()
Dim rColP As Range, i As Range
Dim TheColor As Long
Set rColP = Range("P2", Range("P" & Rows.Count).End(xlUp))
For Each i In rColP
Select Case i.Value
Case 1 To 30: TheColor = 4
Case 31 To 60: TheColor = 6
Case 61 To 90: TheColor = 46
Case 91 To 700: TheColor = 3
End Select
i.EntireRow.Interior.ColorIndex = TheColor
Next i
End Sub
"Rachel Costanza" <Rachel wrote in
message ...
I am trying to create a macro that will color code the rows in a
spreadsheet.

The days for reference are in column O,P.

For days between 1-30 i need the rows to turn green
For days between 31-60 i need the rows to turn yellow
For days between 61-90 i need the rows to turn orange
For days between 91-700 i need the rows to turn red.

Thanks in advance for your time.

Rachel
In the line of code I just sent you, change the 1 to a 4. Leave the 16
alone. HTH Otto
"Rachel Costanza" wrote in
message ...
Thanks Otto!

How can I make the color coding start in column D?

"Otto Moehrbach" wrote:

Rachel
Find the line of code:
i.EntireRow.Interior.ColorIndex = TheColor
and replace it with:
Range(Cells(i.Row, 1), Cells(i.Row, 16)).Interior.ColorIndex = TheColor
all in one row.
HTH Otto

"Rachel Costanza" wrote in
message ...
This is great! What is the code to stop the colors from streaming into
rows
after column P ?

"Otto Moehrbach" wrote:

Rachel
Something like this perhaps. I assumed that the reference numbers
are
in Column P. HTH Otto
Sub ColorRows()
Dim rColP As Range, i As Range
Dim TheColor As Long
Set rColP = Range("P2", Range("P" & Rows.Count).End(xlUp))
For Each i In rColP
Select Case i.Value
Case 1 To 30: TheColor = 4
Case 31 To 60: TheColor = 6
Case 61 To 90: TheColor = 46
Case 91 To 700: TheColor = 3
End Select
i.EntireRow.Interior.ColorIndex = TheColor
Next i
End Sub
"Rachel Costanza" <Rachel wrote in
message ...
I am trying to create a macro that will color code the rows in a
spreadsheet.

The days for reference are in column O,P.

For days between 1-30 i need the rows to turn green
For days between 31-60 i need the rows to turn yellow
For days between 61-90 i need the rows to turn orange
For days between 91-700 i need the rows to turn red.

Thanks in advance for your time.

Rachel
You can use formulas that look at the numbers. To count colors would
take VBA. For instance, to get the number of rows that have 31 to 60, use:
=CountIf(P2:P50,"<=60") - CountIf(A1:A50,"<=31")
Otto

"Rachel Costanza" wrote in
message ...
Also, how do I count the number of rows assigned to each color (red,
yellow,
orange, green) i.e 14 Red, 20 Green as of a constant date?

"Otto Moehrbach" wrote:

Rachel
Something like this perhaps. I assumed that the reference numbers
are
in Column P. HTH Otto
Sub ColorRows()
Dim rColP As Range, i As Range
Dim TheColor As Long
Set rColP = Range("P2", Range("P" & Rows.Count).End(xlUp))
For Each i In rColP
Select Case i.Value
Case 1 To 30: TheColor = 4
Case 31 To 60: TheColor = 6
Case 61 To 90: TheColor = 46
Case 91 To 700: TheColor = 3
End Select
i.EntireRow.Interior.ColorIndex = TheColor
Next i
End Sub
"Rachel Costanza" <Rachel wrote in
message ...
I am trying to create a macro that will color code the rows in a
spreadsheet.

The days for reference are in column O,P.

For days between 1-30 i need the rows to turn green
For days between 31-60 i need the rows to turn yellow
For days between 61-90 i need the rows to turn orange
For days between 91-700 i need the rows to turn red.

Thanks in advance for your time.

Cells(r, c) means row and column

Range(Cells(i.Row, 4), Cells(i.Row, 16)).Interior.ColorIndex = TheColor

4 is D and 16 is P on whichever row the i in the For..Next loop is on


Gord Dibben MS Excel MVP

On Tue, 18 Aug 2009 12:38:01 -0700, Rachel Costanza
wrote:

Thanks Otto!

How can I make the color coding start in column D?

"Otto Moehrbach" wrote:

Rachel
Find the line of code:
i.EntireRow.Interior.ColorIndex = TheColor
and replace it with:
Range(Cells(i.Row, 1), Cells(i.Row, 16)).Interior.ColorIndex = TheColor
all in one row.
HTH Otto

"Rachel Costanza" wrote in
message ...
This is great! What is the code to stop the colors from streaming into
rows
after column P ?

"Otto Moehrbach" wrote:

Rachel
Something like this perhaps. I assumed that the reference numbers
are
in Column P. HTH Otto
Sub ColorRows()
Dim rColP As Range, i As Range
Dim TheColor As Long
Set rColP = Range("P2", Range("P" & Rows.Count).End(xlUp))
For Each i In rColP
Select Case i.Value
Case 1 To 30: TheColor = 4
Case 31 To 60: TheColor = 6
Case 61 To 90: TheColor = 46
Case 91 To 700: TheColor = 3
End Select
i.EntireRow.Interior.ColorIndex = TheColor
Next i
End Sub
"Rachel Costanza" <Rachel wrote in
message ...
I am trying to create a macro that will color code the rows in a
spreadsheet.

The days for reference are in column O,P.

For days between 1-30 i need the rows to turn green
For days between 31-60 i need the rows to turn yellow
For days between 61-90 i need the rows to turn orange
For days between 91-700 i need the rows to turn red.

Thanks in advance for your time.

Thanks Otto,

How do I use CountIf when I am looking to count numbers between 30 and 60?


"Otto Moehrbach" wrote:

Rachel
You can use formulas that look at the numbers. To count colors would
take VBA. For instance, to get the number of rows that have 31 to 60, use:
=CountIf(P2:P50,"<=60") - CountIf(A1:A50,"<=31")
Otto

"Rachel Costanza" wrote in
message ...
Also, how do I count the number of rows assigned to each color (red,
yellow,
orange, green) i.e 14 Red, 20 Green as of a constant date?

"Otto Moehrbach" wrote:

Rachel
Something like this perhaps. I assumed that the reference numbers
are
in Column P. HTH Otto
Sub ColorRows()
Dim rColP As Range, i As Range
Dim TheColor As Long
Set rColP = Range("P2", Range("P" & Rows.Count).End(xlUp))
For Each i In rColP
Select Case i.Value
Case 1 To 30: TheColor = 4
Case 31 To 60: TheColor = 6
Case 61 To 90: TheColor = 46
Case 91 To 700: TheColor = 3
End Select
i.EntireRow.Interior.ColorIndex = TheColor
Next i
End Sub
"Rachel Costanza" <Rachel wrote in
message ...
I am trying to create a macro that will color code the rows in a
spreadsheet.

The days for reference are in column O,P.

For days between 1-30 i need the rows to turn green
For days between 31-60 i need the rows to turn yellow
For days between 61-90 i need the rows to turn orange
For days between 91-700 i need the rows to turn red.

Thanks in advance for your time.

Isn't that what Otto gave you?

(well, he used 31, but I assume you could make that adjustment)

Rachel Costanza wrote:
Thanks Otto,

How do I use CountIf when I am looking to count numbers between 30 and 60?


"Otto Moehrbach" wrote:

Rachel
You can use formulas that look at the numbers. To count colors would
take VBA. For instance, to get the number of rows that have 31 to 60, use:
=CountIf(P2:P50,"<=60") - CountIf(A1:A50,"<=31")
Otto

On Aug 18, 7:17 pm, Rachel Costanza <Rachel
wrote:
I am trying to create a macro that will color code the rows in a spreadsheet.

The days for reference are in column O,P.

For days between 1-30 i need the rows to turn green
For days between 31-60 i need the rows to turn yellow
For days between 61-90 i need the rows to turn orange
For days between 91-700 i need the rows to turn red.

Thanks in advance for your time.

Hi Rachel,
an interesting article on heat maps is
http://www.clearlyandsimply.com/clea...eat-a-map.html

Hope that helps; have fun
Cheers
Michael

Does anyone know how to use this macro and add lines of data. i can only get this macro to work on 23 rows but I need a few more.

Thanks



Posted as a reply to:

Macro for HEAT chart - THANKS IN ADVANCE!

I am trying to create a macro that will color code the rows in a spreadsheet.

The days for reference are in column O,P.

For days between 1-30 i need the rows to turn green
For days between 31-60 i need the rows to turn yellow
For days between 61-90 i need the rows to turn orange
For days between 91-700 i need the rows to turn red.

Thanks in advance for your time.

EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorials...vices-usi.aspx

Peter
When do you want this, the coloring of rows, to happen? When you enter
a number in one of those cells? When you save the file? Close the file?
You say the numbers are in "column O, P". That's two columns. Did you mean
to say both columns? If so, can numbers be entered into both columns? If
so, and the two numbers conflict (two different colors), what do you want to
happen? In what row does your data start? HTH Otto
<peter brink wrote in message
...
Does anyone know how to use this macro and add lines of data. i can only
get this macro to work on 23 rows but I need a few more.

Thanks



Posted as a reply to:

Macro for HEAT chart - THANKS IN ADVANCE!

I am trying to create a macro that will color code the rows in a
spreadsheet.

The days for reference are in column O,P.

For days between 1-30 i need the rows to turn green
For days between 31-60 i need the rows to turn yellow
For days between 61-90 i need the rows to turn orange
For days between 91-700 i need the rows to turn red.

Thanks in advance for your time.

EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorials...vices-usi.aspx

Post the code.

I can't see anything to add to.


Gord Dibben MS Excel MVP

On Thu, 17 Sep 2009 10:54:10 -0700, peter brink wrote:

Does anyone know how to use this macro and add lines of data. i can only get this macro to work on 23 rows but I need a few more.

Thanks



Posted as a reply to:

Macro for HEAT chart - THANKS IN ADVANCE!

I am trying to create a macro that will color code the rows in a spreadsheet.

The days for reference are in column O,P.

For days between 1-30 i need the rows to turn green
For days between 31-60 i need the rows to turn yellow
For days between 61-90 i need the rows to turn orange
For days between 91-700 i need the rows to turn red.

Thanks in advance for your time.

EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorials...vices-usi.aspx