Solved Color changes to a cell using IF

August 8, 2014 at 09:33:24
Specs: Windows 7
 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

See More: Color changes to a cell using IF

#1
August 8, 2014 at 19:11:17
 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.

Report •

#2
August 8, 2014 at 19:14:13
 What your looking for is Conditional Formatting.Without knowing how your sheet is arraigned.If column A determines if they are Full or Part TimeandIf 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 20071) Select your cell or range of cells: A1:B72) On the ribbon click Conditional Formatting3) Click on New Rules, itâ€™s near the bottom of the dialog box.4) Click Use 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 Format button7) Select the Fill Tab8) Select a pretty color9) Click OK10) Click OKThe dollar sign symbols are necessary if you want the row to highlight.See how that works for you.MIKEmessage edited by mmcconaghy

Report •

#3
August 11, 2014 at 07:29:13
 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?

Report •

Related Solutions

#4
August 11, 2014 at 08:05:42
 Post your formula so we can see what you've got.MIKEhttp://www.skeptic.com/

Report •

#5
August 11, 2014 at 08:10:29
 +OR(AND(\$W6="FT",\$X6<38),AND(\$W6="PT",\$X6>28))FT 37:0FT 32:0FT 24:0FT 40:0PT 32:0Where the first column is X and the numbers are Y. The first 4 come up red, and the 5th is blank...

Report •

#6
August 11, 2014 at 09:20:23
 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 thatExcel 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.MIKEhttp://www.skeptic.com/

Report •

#7
August 11, 2014 at 09:46:22
 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!

Report •