Cell colouring macro.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ceab - 12 Sep 2007 12:12 GMT

I have a row with a bunch of numbers from 1 to 10.
I need a macro (or something) that colours the cell green for numbers less
than or equal to 5, yellow for 6-8 and red for 9-10.
How can I do this?

Any help appreciated.

Reply to this Message

OssieMac - 12 Sep 2007 12:50 GMT

Look up 'Conditional Formatting'. You can use the formula option to determine
the color required.

Regards,

OssieMac

> I have a row with a bunch of numbers from 1 to 10.
> I need a macro (or something) that colours the cell green for numbers less
> than or equal to 5, yellow for 6-8 and red for 9-10.
> How can I do this?
>
> Any help appreciated.

Reply to this Message

Dave Peterson - 12 Sep 2007 12:53 GMT

It sounds you could use format|conditional formatting to do this.

You can read Debra Dalgleish's notes starting here:
http://contextures.com/xlCondFormat01.html

Signature

Dave Peterson

Reply to this Message

Rick Rothstein (MVP - VB) - 12 Sep 2007 15:31 GMT

>I have a row with a bunch of numbers from 1 to 10.
> I need a macro (or something) that colours the cell
> green for numbers less than or equal to 5, yellow for
> 6-8 and red for 9-10.
> How can I do this?

Select all of the cells in the row that will need to have this functionality
(if you are not sure, and these numbers are the only ones that will be in
the row, then select the entire row). Note which cell is the highlighted one
(it will be the non-shaded one)... for my example, I am assume we are
talking about Row A and that A1 is the highlighted cell (you will use the
address for the highlighted cell in your own selection in place of the A1
that I use). With the range selected, click on Format/ConditionalFormatting
in Excel's menu bar. Click the Add button twice on the dialog box that
appears (so that you will see three "Conditions" listed. In the first combo
box in each condition, select "Formula Is". Put these formulas into the
indicated "Conditions"...

Condition 1: =AND(A1<>"",A1<=5)

Condition 2: =AND(A1<>"",A1<=8)

Condition 3: =AND(A1<>"",A1<=10)

Next, click the format button for Condition 1, click the Patterns tab on the
dialog box that appears and select the shade of green that you want the cell
to be colored in if the value is less than or equal to 5. Hit OK. Do the
same thing for Condition 2 (selecting a shade of yellow) and Condition 3
(selecting a shade of red). Once you have finished that, click OK on the
Conditional Formatting screen and the cells you highlighted will color in
correctly when the numbers you indicated are entered into them.

Rick

Reply to this Message

learning quickly - 12 Sep 2007 19:40 GMT

Can this be done with multiple rows? I have a spreadsheet that is finding the
max number in a row for each row. I want for each row the highlight the max
number. EG:E4=max(m4:ap4) and whatever number is populated in column E I want
that number in columns m through ap for that particular row to highlight.

Thank you

> >I have a row with a bunch of numbers from 1 to 10.
> > I need a macro (or something) that colours the cell
[quoted text clipped - 29 lines]
>
> Rick

Reply to this Message

Rick Rothstein (MVP - VB) - 13 Sep 2007 04:36 GMT

Yes, you can apply Conditional Formatting across multiple rows, multiple
columns and both multiple rows and multiple columns together. I'm not 100%
sure about what you are asking (what is EG:E4?), but let me take a guess.
You want to highlight two different things in **each** row... the maximum
value and those values that are equal to the value in column E of the same
row. Because the format for these will be different, you will need to decide
how to handle the situation when a cell contains a value that is equal to
the value in column E of the same row and that value also happens to be the
maximum in the row also.

Here is what I came up with. The maximum value(s) will be highlighted in
red, the value(s) equal to the the value in column E of the same row will be
highlighted in green and those cells that are both a maximum and equal to
the column E value will be hatched in the colors red and green.

Okay, select all the cells from column M through column AP and down for as
many rows as you will need to eventually handle. With the range selected (do
it so M1 is the highlighted cell in this range), click on
Format/ConditionalFormatting in Excel's menu bar. Click the Add button twice
on the dialog box that appears (so that you will see three "Conditions"
listed. In the first combo box in each condition, select "Formula Is". Put
these formulas into the indicated "Conditions"...

Condition 1: =AND(M1=$E1,M1=MAX($M1:$T1),$M1<>"")

Condition 2: =AND($M1=MAX($M1:$T1),$M1<>"")

Condition 3: =AND($M1=$E1,$M1<>"")

Next, click the Format button for Condition 1, click the Patterns tab on the
dialog box that appears and select a cross-hatch pattern and (while the
pattern dialog is open) pick a light red color for the cross-hatch color;
then click OK and pick a light green color from the Cell shading area for
the cell's background color; finally, click OK to lock in that format. Next,
click the Format button for Condition 2 and pick the same light red color
you used in Condition 1; then click OK. Do the same for Condition 3, but
this time select the same light green color you used in Condition 1. Once
you have finished that, click OK on the Conditional Formatting screen and
the cells you highlighted will color in as I describe in the beginning of
this process.

Rick

> Can this be done with multiple rows? I have a spreadsheet that is finding
> the
[quoted text clipped - 45 lines]
>>
>> Rick

Reply to this Message