Multiple Conditional Formatting based on MONTH()

[MSegas]

Hi, I am trying to create a conditional formatting that will create unique color for each month on Column C.  

• Column A is the DATE value in short format
• Column B sets the YEAR based on calculation and is conditionally formatted with color scale
• Column C sets the MONTH based on a calculation and using "mmm", but color scale does not work

I have created 12 formulas based on the MONTH() value with different colors, but the rules of precedence are preventing the formula to take effect in all rows, and if there is a change if the sort order of the date, year or month, then the colors will not work. 

How can I resolve this?

Additionally, if more rows are added on inserted how can I ensure the conditional format applies to the new rows.

Thank you

[#2 · Accepted solution]

@MSegas 

Try this formula in C2 and fill down.

=DATE(2000,IF(A2>=DATE(YEAR(A2),10,15),1,MONTH(A2)+1),1)

Apply a standard color scale.

[#3]

@MSegas Perhaps I misunderstood, but the CF rules for column C could be like this:

=MONTH($C2)=1

=MONTH($C2)=2

=MONTH($C2)=3

and so on.

[#4]

@Hans Vogelaar 

WOW ! what a nifty and simple solution. Thank you.!

Since my date column expands to 1900, I'll use 1900 instead of 2000.

I noticed that in the second part of the formula you removed the DATE YEAR repetition. Instead of ... , DATE(YEAR(A2),MONTH(A2)+1 your formula uses only MONTH(A2)+1

Your formula: =DATE(2000,IF(A2>=DATE(YEAR(A2),10,15),1,MONTH(A2)+1),1)

My formula:   =IF(A2>=DATE(YEAR(A2),10,15),1,DATE(YEAR(A2),MONTH(A2)+1,DAY(1)))

Your formula is cleaner than mine.

Thanks again.

[#5]

Humm, I did not try that, but the answer from Hans Vogelaar, worked.

[#6]

@MSegas 

It's not really necessary to use 1900. Any year would do.

[#7]

I see. The year is irrelevant because of the if statement