Applying conditional formatting to multiple ranges based on the value of one cell

[chrisneu]

I have a calendar sheet. Each month is a separate block of cells (one cell = one day). There is another separate cell one the sheet where I want to hand-type a random date. I want the conditional formatting to be applied to the correct cell within the correct block based on the date that was hand-typed in that separate cell. As an example, the ranges of two blocks of cells (two months) are $C$5:$I$10 and $K$5:$Q$10. The location of the separate cell is $AQ$15. The conditional formatting that I currently have is "Format only cells that contain" with the cell value equal to =$AQ$15 and it applies to =$C$5:$I$10. How do I add the other block of cells as a separate range to the same rule? I know I can create separate rules for each block (month), but in that case I will end up with quite a number of rules.

[leo73pk]

Chirsneu,

you can achieve your goal by defining multiple ranges separated by comma (,) in applies to range. In this manner you can apply same rule to multiple ranges.

[chrisneu]

Hi, leo73pk! Thanks for responding. I tried to separate ranges by comma, but Excel removes the added ranges as soon as I apply the change and returns to previously saved single range.

[leo73pk]

Do attach your file so members can have a look at the problem

[chrisneu]

leo73pk, I just tried to add multiple ranges by selecting ranges with the mouse and separating each one with a comma and it worked! I tried copy and past the ranges separated by comma multiple times before, but I guess it only works when you actually select ranges. Thank you! The problem is solved.