Conditional formatting (Excel 2000)

[mikeyt]

Hi

I am looking for advice to do some conditional formating

I have a sheet which contains the following information
Persons Name
Week 1 hours
Week 2 hours
Week 3 hours
Week 4 hours
Total of hours weeks 1 to 4
Week 5 hours
Week 6 hours
Week 7 hours
Week 8 hours
Total of hours weeks 5 to 8

I would like to colour the name Red either of the 2 totals are greater than 100%, Yellow if either of the totals is between 80% and 100%, finally Green if both are <80%. The % is calculated based on total hours over 160 hours per week. I would like to know if it can be done without writing and macro.

Thanks for any pointers

Mike

[SammyB]

You want to use Format | Conditional Formating with three conditions (this example is for cell B1:
Condition 1, Formula Is =OR(B6>160,B11>160), Format is Red Font
Condition 2, Formula Is =OR(B6>=128,B11>=128), Format Is Yellow Font
Condition 2, Formula Is TRUE, Format Is Green Font

Then you can use the Format Painter to copy the format to the other names. This is confusing the first couple of times. I've attached a sample workbook. HTH --Sam

[mikeyt]

Thanks Sam,

I will try this first thing in the morning.

Regards

Mike

[mikeyt]

Thanks Sam,

That worked fine if I use fixed values, the only problem I had was if I tried to use a calulation eg =OR(((B6/$H$1)*100)>100,((B11/$H$1)*100)>100), I always got the resul of the first condition.

Regards

Mike

[SammyB]

What's in H1?

[mikeyt]

Hi Sam,

H1 would contain the number of hours per month and can vary depending on the number of weeks in a month. So I would use H1 for calculations instead of puting a fixed value in the calculation.


Regards

Mike

[SammyB]

Your formula looks OK, but I would use the following instead for clarity: (Also see attachment)
<pre>=OR(B6/$H$1>100%,B11/$H$1>100%)
=OR(B6/$H$1>=80%,B11/$H$1>=80%)
TRUE</pre>

[mikeyt]

Thanks Sam......................... Mike