Highlight a row one colour and then another after a period of time.

[stuj77]

I have created a formula in column F to state 'Out of date' when the date in column D is less than 'todays' date in column F1. With help I have managed to highlight the row in red when this happens.

What I would like to happen is that 30 days before the expiry date the row highlights orange to prompt me to reorder the item. Then after the expiry date the row then turns red.

Any help would be amazing.

I have attached the document.

SundryTest.xlsx

Thanks.

[FDibbins]

Create a new rule and use basically the same formula, just take 30 away from the date...
=AND($F$1<>"",$D4<>"",$D4<$F$1-30)

[stuj77]

Sorry to sound simple.... Where would I create a new rule? A conditional formating rule?

Thanks

[FDibbins]

Nope, not simple at all, my fault for not explaining better

You already have 1 Conditional Formatting rule in there, you can add others the same way you added the 1st 1

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter ==AND($F$1<>"",$D4<>"",$D4<$F$1-30)

(for the ranges, you could just copy/paste from the 1st rule)

[stuj77]

I can't get it to work. If cell G states reorder. I would like the cells in that row (A:E) to be highlighted orange. When out of date appears in cell F I want the rows to be red. Sorry does this make sense?

[FDibbins]

duh I I read your request wrong, it should be this...
=AND($F$1<>"",$D4<>"",$D4<=$F$1+30)

And make sure that the "red" rule is the 1st rule

[stuj77]

I think it's my limited way of explaining what I want :/ Row 4 for example Needs to be highlighted orange when reorder appears in cell G4 and highlighted red when out of date appears in cell F4

The same needs to happen in row 5 but the reorder date might be 60 days, rather than 30. (determined by forumla in cell G5)

Sorry I am a real pain

SundryTest.xlsx

[FDibbins]

Not a pain at all

your rules were...

What I would like to happen is that 30 days before the expiry date the row highlights orange to prompt me to reorder the item. Then after the expiry date the row then turns red

The CF rules that you have in that table will trigger when the conditions you stated are met. Rows 4, 6 14, 15 are all within 30 days of F1, so they turn orange - all the other dates fall later than 30 days past "today", so no color change happens
As a test, if you change D5 to 7 Oct 2014, that row turns red.

Im not sure where the contents of G now come in though?

[stuj77]

What you state is correct and I fear my explination of the problem was limited in the first instance. The CF rule you have helped me with does work but not in the way I require it to.

The formula of G will be set for each row. I have only so far done rows 4 & 5. Like I explained the reorder date (G) could be anywhere from 1 to 100 days so the formula in G will be slightly different for each row. In my origional post I should of stated I was using '30' as an example and that it would not be necessarly 30 in each row.... (if that makes sense)?

So I need the rows A:E to change to orange when reorder appears in cell G. And then to Red when Out of date appears in cell F.

Sorry I am new to this

[FDibbins]

OK, that makes the rules even simpler

rule 1 =G2="Reorder"
rule 2 =F2="Out of Date"

Have you considered combining F and G into 1 formula? Or could it be out of date and still need to be reordered?

[stuj77]

SundryTest.xlsx
Doesnt seem to work - Am I applying the rule correctly?

[stuj77]

Hi - I think I have managed to solve it with your help using the formula below! Thank you!
=$G4="REORDER"
=$F4="OUT OF DATE"

[FDibbins]

Great, looks like I had the row number wrong, sorry Glad you got it working though