Conditional formatting help needed.

Hi All,
I need advice on how to achieve the following:
On the attached example I want to control the appearance of column I based on the difference between the date in column G and the current date and information in column J.

e.g.
If the date in G4 is in the past I want I4 to have a red background and say Overdue, unless J4 says Yes, in which case it is Green and says approved.
If the date in G4 is the same as or within the next 3 days from the current date - I4 to be Orange and say Pending.
If the date in G4 is within the next 4 to 7 days from the current date - I4 to be Yellow and say Pending.
If the date in G4 is more than 7 days after the current date - I4 to have no fill and say Issued.
If J4 says yes in any instance I4 to be Green and say approved

Hopefully that makes sense!
I’m a novice at excel and have tried unsuccessfully to do the above with conditional formatting which is hopefully the correct way of doing it.

Thanks for any help you can give.
Aidy

Current date

Hi richard,
Thanks for that. Works a treat.
Is there a way of tweaking it slightly so that - If J4 says yes in any instance I4 to be Green and say approved only if a date has been added to column H?

No colour of wording required

Originally Posted by Richard Buttrey

...and if there is no date in column H, what colour and text then?

that should have been colunm G - my brain is not functioning!

Thanks,
one last tweak, can I4 stay empy until a date is put in E4?