Inserting color into complex IF formula

I have a vacation calculator/ calendar that I am working on and need help with my last formula. To exp-lain why my formula is so complicated: Our vacation for 2012 carries over to April 1st 2013, after that all but 10 days expire. Those 10 days then expire on June 30th. On top of that, we get our new 2013 vacation on April 1st. With that said, the below is a written our version of what I need the formula to look like:

If H2 is greater than 0 (meaning the person has 2012 vacation to carry over) then E16:E48, G16:G48, AND I16:I48 (these cells are the calendar) Should be the color blue (to represent 2012 vacation being used). This should only happen if the number 1 or .5 is typed into E16:E48, G16:G48, or I16:I48. And this should only happen up to the amount in cell H2 (the amount of days carrying over from 2012).

Please attach a workbook with a clear before and after.

See how this works...

Conditional Formatting

• Highlight applicable range >> E16:E48
• Home Tab >> Styles >> Conditional Formatting
• New Rule >> Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =AND(OR(E16=1,E16=0.5),$H$2>0)
• Format… [Number, Font, Border, Fill]
• Applies to >> =$E$16:$E$48,$G$16:$G$48,$I$16:$I$48
• OK >> OK

See if this helps on the After tab...

Hi Ashley,

It would help to see the formula you are working with and/or the show the attachment so I can see the formula there...

Originally Posted by Ash87

If H2 is greater than 0 (meaning the person has 2012 vacation to carry over) then E16:E48, G16:G48, AND I16:I48 (these cells are the calendar) Should be the color blue (to represent 2012 vacation being used). This should only happen if the number 1 or .5 is typed into E16:E48, G16:G48, or I16:I48. And this should only happen up to the amount in cell H2 (the amount of days carrying over from 2012).

Hi Ashley,

The attachment in post #6, how is it not working right based on your requirements?

In H2, as long as the number is greater than 0 the cells remain blue, but as soon as that number in H2 reads 0 then everything is black.

I guess I must not be following what you actually need.

Hi Ashley,

That does make a little more sense, but my day is pretty busy at work so I'll look into it later to see if there is a viable solution.

hi Ash87. not sure if you are using Excel 2003 as your profile mentioned. not a very good solution. i made the formula only possible for Jan-Mar. formula at E16 to be:

Please Login or Register  to view this content.

basically, my 1st SUM is a variable range that increases as it goes down & changes columns when it moves to the right.
my 2nd SUM is fixed at the whole range of column E, but only sums when looking at column G, which is Feb. this is to add Jan data only in Feb
my 3rd SUM is the same as the 2nd one, but fixing at column G (Feb), when looking at column I (Mar). add Feb data only in Mar

besides that, i make sure the entries are numbers & H2 is more than 0

it would actually be better if all your cells are actually dates, not numbers. that would help you not manually key in. but please raise it in another thread if you need.