Conditional formatting, date and drop down list criteria

[Pritchard78]

I have a tracker used to track the start and end dates of various tasks. Columns F and G are the start and end dates, respectively. Column I has a drop down list with various selections like "On Track", "Not started" etc.

The headers for columns J through to AU are dates plus 1 day and use cell E5 (contract start date) for reference.

I have conditional formatting set up to highlight cells between columns J and AU based on dates selected in F and G. What id like to do is have the colour be dependant on the selection from the drop down in column I.

As an example:
E5 is 01/01/20
J7 would be 02/01/20, J8 would be 03/01/20 etc
F9 is 02/01/20
G9 is 06/01/20
Cells J9, K9 and L9 will highlight Dark grey
Now if i choose "On Track" from the list in I9, i can get the cells F9 and G9 to change green - i want the Dark Grey to correspond to the colours in cells F9 and G9.

Is this possible?

Any help appreciated

 

[etaf]

what is the conditional formatting formula you are using

Now if i choose "On Track" from the list in I9, i can get the cells F9 and G9 to change green - i want the Dark Grey to correspond to the colours in cells F9 and G9.

You can setup different rules for fill colours
use an AND () to include the items in the dropdown list

=AND ( formula used to produce the dark grey , I2 = " item from dropdown list)

 

[Pritchard78]

what is the conditional formatting formula you are using

You can setup different rules for fill colours
use an AND () to include the items in the dropdown list

=AND ( formula used to produce the dark grey , I2 = " item from dropdown list)

Formula im using is:

=AND($F9<=J$7,$G9>=J$7)

Is it possible to use multiple criteria with the AND operator or just two?

 

[etaf]

multiple YES - not sure of limit, maybe something to do with how many characters are allowed in version of excel you are using , NOT the number of elements
not sure of what cell the drop down is in - assuming I9
add a new formula and fill GREEN - you may need to order this one 1st and stop if TRUE

=AND($F9<=J$7,$G9>=J$7,$I9 = "ON TRACK")
then it will NOT move to next rule if TRUE

 

[Pritchard78]

multiple not sure of what cell the drop down is in - assuming I9
add a new formula and fill GREEN - you may need to order this one 1st and stop if TRUE

=AND($F9<=J$7,$G9>=J$7,$I9 = "ON TRACK")
then it will NOT move to next rule if TRUE

Tried that, colour part not working

 

[Pritchard78]

Tried that, colour part not working

I know what i was doing wrong, it was changing the cell references when copying the formula to new conditions for some reason.

Thanks for your help!

 

[etaf]

What order are the conditional formatting rules
can i see that part
why F9 & F7

 

[etaf]

glad its worked out ok