• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting based on a range AND a drop down list


New Member
Hello there.

I am currently making my own timeline and have successfully manged to get a row to highlight in a color based on a start date and end date ---

Essentially Row A displays the days of the month (A3 - 8/1, B3 = 8/2 and so on) and in Row 2 A2 is the start date and B2 is the end date. So if the start date is 8/1 and end date is 8/2 then A3 and B3 will fill in a color based on this conditional formatting statement: =AND(A$3>=$A2,A$3<=$A3).

Now what I want to do is add more formatting where the cells change color based on the above condition and if I change the status based on a drop down list (My list is based on a validation list and has values like 'Delayed', 'Planned' and so forth......I don't see how I can do this.

Any help is greatly appreciated.


It is kind of difficult to envision your spreadsheet just by the above description. However lets give it a shot. First of all one must always identify the version of MS Excel in use, knowing the version is critical in attempting to provide assistance.

One possible way is to add another reference in your conditional formating formula, which points to the cell that shows the choice based on data validation list.

If you are using Excel 2003 you are limited to use only three conditions when it comes to conditional formating.

So you have to make sure that your data validation choices also follow the same rule.

Let us know how did it work out.