Author: Oscar Cronquist Article last updated on August 05, 2017

Steven asks:

I got 6 events with different dates...
On sheet 2 i have a Year Calander (365 Days)I need to do a conditional formatting to highlight the days which i have events.The 6 is just a start and the list will go longer...I want to have a pictorial view of the calendar on where the events fall in the year which is SHEET 2.
Sheet 1 i just key in the dates for the start and end....


highlight events in a calendar6

How to change year

You can change the year in cell K2 and the calendar changes almost instantly.

How to add or remove events

The events are in an excel defined table. You can add or remove rows by right clicking on a cell and select Insert or Delete.

highlight events in a calendar2

You can also add a blank row by selecting the last cell in the table.

highlight events in a calendar3

Press Tab key.

highlight events in a calendar4

You can move the table to any sheet you like.

How to change formatting color

  1. Select cell range B6:X38
  2. Go to tab "Home"
  3. Click "Conditional formatting" button
  4. Click "Manage Rules..."
  5. Select the rule with grey formatting
  6. Click "Edit Rule..." button
  7. Click "Format..." button
  8. Go to tab "Fill"
  9. Pick a different color
  10. Click OK
  11. Click OK

Download excel *.xlsx file

Highlight events in a calendar v3.xlsx