Macro for HEAT chart - THANKS IN ADVANCE!

[Rachel Costanza]

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.

 

[JP Ronse]

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

 

[Otto Moehrbach]

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]

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

 

[Rachel Costanza]

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]

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]

Thanks Otto!

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

 

[Otto Moehrbach]

Rachel
In the line of code I just sent you, change the 1 to a 4. Leave the 16
alone. HTH Otto

 

[Otto Moehrbach]

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(P250,"<=60") - CountIf(A1:A50,"<=31")
Otto

 

[Gord Dibben]

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

 

[Rachel Costanza]

Thanks Otto,

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

 

[Glenn]

Isn't that what Otto gave you?

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

 

[Michael.Tarnowski]

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/cle...there-is-more-than-one-way-to-heat-a-map.html

Hope that helps; have fun
Cheers
Michael

 

[peter brink]

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 gree
For days between 31-60 i need the rows to turn yello
For days between 61-90 i need the rows to turn orang
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/tutorial...a-6dafb17b6d74/wcf-workflow-services-usi.aspx

 

[Otto Moehrbach]

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

 

[Gord Dibben]

Post the code.

I can't see anything to add to.


Gord Dibben MS Excel MVP