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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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)
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Solution
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: 8
Upvote 0
What order are the conditional formatting rules
can i see that part
why F9 & F7
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top