How to Highlight Calendar Day (whole column) with Date from another Sheet?

[joelmoor]

Hello,

So i'm building an automated Calendar and i came across a problem. This is the setting:

Worksheet "Dates" where you type in the Dates that are going to be highlighted in the Calendar when its created. One Place to type in a Date would be G10 in "Events".
events.PNG

And of course the Worksheet "Calendar".
The Calendar itself is just a horizontal list of all Workdays in a year starting at E3 ending at JN3

I thought i could solve this with conditional formatting, but i have to idea where to start... The idea is that the whole column lets say for the Date 11.11.2019 is highlighted. A Column goes from Row 4 to 25

COLUMN2.PNG

I'm thankful for any piece of advice

Joel Moor

[Pete_UK]

Select all the cells that you want this to apply to (i.e. E4:JN25), then click on Conditional Formatting | New Rule | Use a formula... , then put this formula in the pop-up dialogue box:

=E$3=TODAY()

Click on the Format button | Fill tab and choose your colour. Then click OK twice to exit the dialogue box, and Excel will automatically adjust that cell reference to suit the other columns.

Hope this helps.

Pete

[joelmoor]

Thank you for the answer!

But how can i reference now the date on the other sheet, so it highlights the given date? With this formula it highlights today.

Joel Moor

[Pete_UK]

I thought that was what you wanted. If you have a cell in another sheet in which you can change the date and want to use that to highlight the appropriate column, then give that cell a name using Name Manager (e.g. Chosen_date ), and then amend the CF formula by selecting cell E4 in the calendar sheet and clicking on Conditional Formatting | Manage Rules then select that rule and click on Edit Rule, and change the formula to this:

=E$3=Chosen_date

Click OK twice to exit. With CF you can't refer explicitly to ranges in other sheets, which is why you need to use a named range.

Hope this helps.

Pete