Color coding dates on a calendar in Excel

[stylencia18]

Hello Forum:

I have a somewhat complicated question. I have 4 sets of dates that I want to color code on a calendar in Excel.

Set 1 Set 2 Set 3 Set 4
1/1/2014 1/5/2014 1/22/2014 1/31/2014
5/5/2015 5/5/2015 6/5/2015 8/5/2015
9/9/2016 10/2/2016 11/2/2016 12/5/2016


I'm looking to have each date populate a different color on a calendar. For example, Set 1 dates are blue, Set 2 dates are green, Set 3 dates are pink and Set 4 dates are purple. The set of dates cant be combined into one long list because each set represents something different.

Is this even possible?


Thank you!!!!

 

[Joe4]

You can use Conditional Formatting to do this.
Set up your four "sets" anywhere in Excel, and then name each range:
rSet1
rSet2
rSet3
rSet4
respectively.
(If you do not know how to name ranges, see: https://www.contextures.com/xlNames01.html)

Then, select all your calendar dates (let's say they start in cell A2), and go to Conditional Formatting, select the Formula option, and enter this formula:

=COUNTIF(rSet1,A2)>0

and then choose the color option that you want.

Repeat the same Conditonal Formatting process for the other three sets. The steps are exactly the same, except change the named range in the formula, and the color that you want.

 

[stylencia18]

Thank you for replying! What happens if 2 of the sets have the same date? Which color will the date turn?

 

[Joe4]

Which Conditional Formatting rule has the higher priority.
Note that when you have multiple rules, you can rank them.