Using a spreadsheet for scheduling, and if an employee is Part Time, should have 32 or fewer hours. Full time should be 40 hours. I want to color code red under 40 for full time and over 32 red for part time. Any ideas? Using Excel 2007

Under 40 should be red, over 32 should be red. That means that every number from negative infinity to positive infinity should be red.

Just fill the cell with Red.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

What your looking for is Conditional Formatting. Without knowing how your sheet is arraigned.

If column A determines if they are Full or Part Time

and

If column B is their Total hours worked, like:A B 1) Full 40 2) Full 35 3) Part 30 4) Part 35 5) Full 45 6) Full 35 7) Part 20

Then something like:Conditional Formatting 2007

1) Select your cell or range of cells: A1:B7

2) On the ribbon clickConditional Formatting

3) Click onNew Rules, itâ€™s near the bottom of the dialog box.

4) ClickUse Formula to determine which cells to format.

5) Enter the formula:=OR(AND($A1="Full",$B1<40),AND($A1="Part",$B1>32))

6) Click on the

Formatbutton

7) Select theFillTab

8) Select a pretty color

9) Click OK

10) Click OKThe dollar sign symbols are necessary if you want the

rowto highlight.See how that works for you.

MIKE

message edited by mmcconaghy

WOW.. lovely formula, and it works fine when i put everything in columns A and B; however, when i modify the formula for my columns W and X, it highlights the first 3 correctly, the 4th incorrectly, and not the 5th at all... I'm dumbfounded. Will it make a difference that the total hours worked is a calculation, and not just a typed in number?

+OR(AND($W6="FT",$X6<38),AND($W6="PT",$X6>28)) FT 37:0

FT 32:0

FT 24:0

FT 40:0

PT 32:0Where the first column is X and the numbers are Y. The first 4 come up red, and the 5th is blank...

The problem is that you are using a TIME, IE 32:00

to represent what should be an integer, the number 32 for 32 Hours.If you take 32:00 and enter it into a cell,

look at the formula bar and you will see that

Excel see's this as 01/01/1900 08:00:00 AMYou should be OK, if you make your TIMES into Integers.

Try this formula:

=OR(AND($W6="FT",($X6*24)<38),AND($W6="PT",($X6*24)>28))

See how that works.MIKE

Mike, you're a genius. It works perfectly. I could have played with that formula all day not realizing the time factor. Thanks so much!

Ask Your Question

Weekly Poll