Conditional formatting, date and drop down list criteria

Pritchard78

New Member
Joined
Nov 18, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,161
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Nov 18, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,161
Office Version
  1. 365
Platform
  1. MacOS
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
 
Solution

Pritchard78

New Member
Joined
Nov 18, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Attachments

  • 20201118_143404.jpg
    20201118_143404.jpg
    101.3 KB · Views: 1

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,161
Office Version
  1. 365
Platform
  1. MacOS
What order are the conditional formatting rules
can i see that part
why F9 & F7
 

Watch MrExcel Video

Forum statistics

Threads
1,118,988
Messages
5,575,390
Members
412,659
Latest member
oliverreyes
Top