Formula related Conditional Formatting

Hi All

Hope everyone is well. I know it can be done because long ago, i've done this but can't remember how.

I'm monitoring submission of work by dates and would like the following

Due dates appear in Column J, and need CF to show those in the past as Red, and those due within 7 days as Blue

however, if the adjacent cell in column K is populated it will alter the colour of the cell in column J (green if 'Passed', orange if 'Submitted / Resub'

As an example :

J3 has a date of 01/07/18 meaning the date is overdue (red) but the learner has submitted work and passed ("Passed" is entered in K3) therefore J3 has now turned green

Does this make any sense? Sorry, it's been a very long day

Thanks in advance

Thanks so much for replying

I'm halfway there but I've not quite got it right, sorry i'm not great with Excel

So I need this CF to apply to the whole column, eg the entry in K3 alters J3, the entry in K4 alters J4 and so on

At the moment, when I type 'Passed' in K2 nothing happens, when I type 'Passed' in K3 the entire J column turns green.

It's something to do with absolute referencing isn't it? Crikey I wish i'd listened at school

GENIUS!!!!!! thank you so much. Really appreciate it. Will add to your reputation now