Blue Flower

How to Automatically Highlight the Background Color of the Cell

The inspiration for this tutorial I found on the internet. For this Excel tutorial, I have chosen a theme related to highlighting the background color of the cell, which contains the date on which an event that we set in the another Worksheet is happening. Our goal is to show all dates in each calendar month during a calendar year and if the date matches the date we have an event, then we want to highlight the cell concerned. To understand this example, it can be used to color a specific date or cells below a given date. There may be more events in one month. Some events last for several days that event occurs between two dates so they have an initial and a final date.

In the picture below you see the worksheet "Calendar" ie. the calendar schedule of each month in the calendar year, with corresponding dates. Dates in a given month are automatically changed and linked to the number of years in the 'A1' cell. Cells whose background colors are 'Red color' indicate that there is a certain event from the list of events on that second working day.

Image 1

Automatic coloring of cells for a particular date in the year

For this example I used the following formulas:

The 'A1' cell contains the number of years (2019) for which we are doing the annual calendar. Dates for a single month are automatically changed, which is done with the formulas.

In the 'C1' cell there is an international code for specific language (This list can be downloaded from this link Specificy a Language Code). So for the English (US) name every month during the year ('A' column, I used the international code '409' (for US format). In the 'A1' cell contains the number of years for which we are doing the annual calendar. Dates for a single month are automatically changed, which is done with the formulas.

Formulas in 'A3', 'A6', 'A9' cells, etc ... are as follows: These cells represent the name of the month in the calendar year based on the international language code '409'. If you are not clear, try setting another code and automatically convert the month's name to another language. (Attached to this tutorial, you have the option to download the Excel Workbook on which I did this tutorial)

'A3' cells:
=TEXT(DATE($A$1;A5;1);"[$-"&$C$1&"]"&month_symbol)

'A6' cells:
=TEXT(DATE($A$1;A8;1);"[$-"&$C$1&"]"&month_symbol)

'A9' cells:
=TEXT(DATE($A$1;A11;1);"[$-"&$C$1&"]"&month_symbol)

If you have a good look at the formula above, you've found that they are linked to the cell below. So in these cells are numbers from 1 to 12 (they are not currently visible because the font is white). These are the following cells: 'A5', 'A8', 'A11', ....., 'A38'

The formula in 'C3' cell is as follows: (The result of this formula is the name of the day of the week. The formula automatically returns the name of the day, depending on the number of years, based on: year number, international language code number, number of months and date number. Copy the formula to the right to the end, ie the 'AG' column).
=IF(C4<>"";TEXT(DATE($A$1;$A5;COLUMN(A1));"[$-"&$C$1&"]"&"ddd");"")

The formula in 'C4' cell is the following:
=DATE($A$1;A5;1)

The formula for the conditional format, which highlight weekend days in the month is the following (rows 3, 6, 9, ..., 36):
=WEEKDAY(C4;2)>5

The formula in 'D4' cell is as follows: (this formula is related to the previous cell of the same order and continues to date by the end of the month, so copy the formula to the right to the end)
=IFERROR(IF(MONTH(C4+1)=MONTH(C$4);C4+1;"");"")

All above is valid for the first month of the calendar year ie "January". You need to copy these Excel formulas for each month below.

Automatically color the background of the cell using Conditional Formatting

We came to the cell row below the Date in the month. These cells we want to highlight the color if the date coincides with the event to another worksheet (My advice is to name the cell range). Some events last for several days, and there are two dates in the event list. I've already written about creating a list of all dates between two dates. Study this tutorial and everything will be clear to you.

So, for Conditional Formatting for the month of January I used the following formula applied to the cell range 'C5:AG5': (apply this formula to each cell range in a row for each calendar month of the year or select multiple rows in one step and apply the formula). Again my advice is to name the cell range in the Worksheet Events '$AK$2:$AK$89'.
=VLOOKUP(C4;Events!$AK$2:$AK$89;1;0)

Events List by Dates in the Year

This worksheet lists a list of events during the 2019 calendar year. Note in the list that some events last one day and some events last for several days. To successfully resolve the highlight of cells for certain events in the 'Calendar' worksheet, here I have to sort all the dates in one column. That's what I wrote on the tutorial List Date Between Two Dates in One Column (study this tutorial). In principle, I extracted all the dates in a given cell range and later created a list of all the dates.

Image 2

List of all events during the calendar year

To make it easier to understand how the formulas work, look at the Evaluate Formula or use the F9 key within the formula. And finally in this way using the Excel formula we can highlight colors of all the cells that refer to a specific Date in which an event is being performed.

Attached to this Excel tutorial, you have the option to download ZIP files
(Automatic Coloring of Cells for a Certain Event in a Calendar Year) that contains the Workbook I used when creating this tutorial. Here's also a file with all international language codes for different language settings (Specific a Language Codes).

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.