Conditional Formatting

[Steve M]

I've set up a spread sheet to track working hours. I used
conditional formatting to highlight/color cells when a
weekly hour threshold was reached. This was to alert the
user to the fact that the worker may have reached his/her
max hours for the week. It works OK with an exception.

Here's what I have done.
The formula:
=sum(B4:H4)>=72
=sum(c4:i4)>=72
=sum(d4:j4)>=72
and so on thru HC4, turning any cells red that meet the
criteria.
In any given 7 day sequence only two of the cells will be
highlighted. E.G., b4:h4 have 12 in each cell, only b4
and c4 will turn red.

What I would like is to have the entire 7 day range turn
red as well as the next two-three cells (i4:k4) to alert
the user that the first few days into the next period are
to be observed also for scheduling issues. This again
would be carried thru to HC4.

Any ideas? Thanks for your help.

Steve

 

[David McRitchie]

Hi Steve,
Hopefully my page on Conditional Formatting would help]
http://www.mvps.org/dmcritchie/excel/condfmt.htm
If not reply to the thread.

You will probably have to make the column part of the
address in your formula absolute so you can color cells in
other columns.

The main points are that the cells that get the color are based
on the selection, and the formula is based on the active cell.