Advanced Date Highlighting Techniques in Excel

This article shows how to highlight dates using Conditional Formatting (CF) techniques. Most of these examples use a CF formula to determine which cells to highlight.

I will demonstrate how to build a conditional formatting formula, how to apply a CF formula to a specific cell range and explain the logic behind the formula step by step.

1. Introduction

What is Conditional formatting?

Conditional formatting is a built-in Excel feature that allows you to format cells based on a condition or criteria. You can access this tool by pressing the "Conditional formatting" button located on tab "Home" on the ribbon.

What is a formatted cell?

A formatted cell in Excel refers to a cell whose contents have been modified in some way to change its appearance or display from the default.

Some examples of formatted cells in Excel:

• Font format - The font type, size, color, bold/italic etc. has been changed.
• Number format - The cell containing a number displays with currency symbols, percentages, dates, or other number formats applied.
• Cell fill color - The background color of the cell has been changed.
• Border - Cell borders have been added to outline or highlight the cell.
• Alignment - The text alignment within the cell has been changed (left, right, center, etc).

What is highlighting a cell?

Highlighting a cell means that something is changed making it different to the other cells, it can be the background color, font size, etc.

Why highlight a cell?

Highlighting a cell makes it easier to spot across rows and columns. This can be handy, for example, to find invalid data or outliers in statistical data, in fact, it can be whatever you want.

What are dates in Excel?

Dates are stored numerically but formatted to display in human-readable date/time formats, this enables Excel to do work with dates in calculations.

For example, dates are stored as sequential serial numbers with 1 being January 1, 1900 by default. The integer part (whole number) represents the date the decimal part represents the time.

This allows dates to easily be formatted to display in many date/time formats  like mm/dd/yyyy, dd/mm/yyyy and so on and still be part of calculations as long as the date is stored numerically in a cell.

You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now shows 5/18/1927.

What is a conditional formatting formula?

A conditional formatting formula allows Excel to dynamically apply formatting only to cells that meet the criteria specified in the formula. This provides much more flexibility than just formatting based on values. A conditional formatting formula can save you a lot of time and effort instead of manually highlighting cells which is tedious and time consuming.

They are entered into the "Formula" box when setting up a conditional formatting rule, they evaluate to either TRUE or FALSE. If the formula evaluates to TRUE, the conditional formatting is applied. If it evaluates to FALSE, the formatting is not applied.

It works almost like a regular formula and you can use most Excel functions like IF, AND, OR, ISBLANK, etc. to build logical tests. They allow conditional formatting based on formulas, not just values. Some common uses are comparing cell values, checking for blank cells, finding duplicates, etc.

What is volatile in Excel?

Volatile in Excel refers to formulas or functions that recalculate whenever any change is made to the workbook, even if the input values they depend on have not changed.

Is conditional formatting volatile?

Yes, they are even "super"-volatile meaning they are recalculated as you scroll a worksheet.

What is the effect of conditional formatting being volatile?

This means conditional formatting may slow down calculations in the rest of the workbook.

2. Highlight dates based on a start and end date

This example demonstrates how to change the background color of cells that meet a date range criteria. A date range has a start and end date in this example. A cell is highlighted if the date is after or equal to the start date and before or equal to the end date.

Cell B3 contains "January 10" and is equal to the start date which meets the condition and makes the background color different than cells that don't meet the criteria.

For example, cell B8 contains "January 29" and is larger than the end date. The criteria is not met and the background color is not changed.

2.1 How to apply the conditional formatting formula

1. Select the range (B3:B11)
2. Press with left mouse button on "Home" tab on the ribbon
3. Press with left mouse button on "Conditional formatting"
4. Press with left mouse button on "New rule..."
5. Press with left mouse button on "Use a formula to determine which cells to format"
6. Press with left mouse button on "Format values where this formula is true" window.
7. Type
   =(B3<=$E$3)*(B3>=$E$2)
8. Press with left mouse button on Format button
9. Press with left mouse button on "Fill" tab
10. Select a color
11. Press with left mouse button on OK!
12. Press with left mouse button on OK!

2.2 Explaining CF formula

Step 1 - Check if current cell date is smaller than or equal to end date

B3<=$E$2

becomes

39823<=39833

and returns TRUE

Step 2 - Check if current cell date is larger than or equal to start date

B3>=$E$2

becomes

39823>=39823

and returns TRUE.

Step 3 - Multiply boolean values

Both boolean values must be TRUE in order to highlight the cell.

Boolean	Boolean	Multiply	Add
FALSE	FALSE	0  (zero)	0 (zero)
FALSE	TRUE	0  (zero)	1
TRUE	TRUE	1	2

The parentheses make sure that the order of calculation is correct.

(B3<=$E$3)*(B3>=$E$2)

becomes

TRUE*TRUE

and returns 1 which is equivalent to TRUE. Cell B3 is highlighted.

3. Highlight dates arranged in a row

This example demonstrates how to highlight dates arranged horizontally in a row. In fact, the conditional formatting formula is exactly the same. There is no difference between this formula and the formula above.

Conditional formatting formula in cell range B6:F6:

This formula is the same as the above (not the cell references though), the difference is that it is applied to a horizontal cell range.

What is a cell reference?

A cell reference lets you "fetch" and use values in other cells in a formula.

There are two types of cell references:

• A1-style reference
• R1C1 reference

The A1-style reference is the default style in Excel, it names columns by letters from A to Z. After Z it starts over with AA, AB, and so on until XFD. Rows are numbered from 1 to 1048576, older Excel versions use less row numbers.

The R1C1-style uses row number and column number like: R1C1, R2C5 and R10C15. Rows are labeled R1, R2, R3 and so on, columns are labeled C1, C2, C3 etc.

The A1-style reference notation is the most common one, here are some examples:

A1 - single cell reference on the same worksheet
A1:D5 - reference to a cell range on the same worksheet
Budget!Z3 - a single cell reference to worksheet Budget
'Budget 2050'!A3 - a single cell reference to a worksheet containing a space character

What types of A1-style cell references are there?

There are two types of cell references:

• Relative cell references
• Absolute cell references

The examples above contain both relative and absolute  relative cell references. The $ dollar character lets you an absolute cell reference meaning you can lock a cell reference horizontally, vertically or both. Here is one example:

A$1 has a relative column reference but an absolute row reference, this means that the column letter may change if the cell is copied and pasted to cells in another column than A. The difference between relative and absolute cell references is important to understand when dealing with conditional formatting formulas.

4. Highlight records

Conditional formatting formula in cell range A7:D16:

This formula is almost the same as the one described in section 1 "Highlight dates based on a a start and end date", see the formula explanation for mor details above in section 1. The cell references are "locked" to column B which is why there is a dollar sign in front of $B7. That makes the entire row highlighted if the date cell is in the date range.

What is a Boolean value?

A Boolean value in Excel is a value that can only be TRUE or FALSE. It represents binary logic and is the result of a logical expression using logical operators or a result of a few Excel functions that I'll discuss below.

Mastering Boolean logic and logical expressions is key to manipulating data and controlling workflow in Excel.

What is binary logic?

Binary logic refers to values having one of two states, TRUE or FALSE. This allows Boolean algebra in Excel using logical operators.

What is a logical expression?

A logical expression is a statement that evaluates to TRUE or FALSE. For example:

=A1<4

These expressions use comparison operators to evaluate a condition and produce a Boolean result.

What are the comparison operators?

= - equal sign
< - less than sign
> - greater than sign

These operators let you build more operators like this:

<> - not equal to
<= - less than or equal to
>= - greater than or equal to

These comparison operators let you create logical expressions like: A2<>5 meaning if the value in cell A2 is not equal to 5, the result is either TRUE or FALSE.

5. Put highlighted values on top

How to sort conditionally formatted cells?

Conditionally formatted cells are great, however, it can be tedious and time consuming to spot highlighted values while scrolling through all data if you work with really large lists.

This trick lets you put highlighted values on top of the list so you can easily find and access them. Here is how you can quickly sort highlighted records to the top of the list:

1. Select cell range A6:C15
2. Press with right mouse button on on cell range
3. Press with left mouse button on "sort"
4. Press with left mouse button on "Put selected cell color on top"

All highlighted records are on top.

Get excel *.xls file

highlight-dates-in-range-using-conditional-formatting2.xls

(Excel 97-2003 Workbook *.xls)

6. How to highlight weekends

Conditional formatting formula:

6.1 How to apply conditional formatting to cell range C3:C20

1. Select cell range C3:C20.
2. Go to tab "Home" on the ribbon.
3. Press with left mouse button on the "Conditional Formatting" button.
4. Press with left mouse button on "New Rule.." to open a dialog box.
   
5. Press with left mouse button on "Use a formula to determine which cells to format".
6. Type the formula. (See above).
7. Press with left mouse button on "Format..." button and choose a formatting.
8. Press with left mouse button on OK button twice.

6.2 Explaining conditional formatting formula

Step 1 - Identify day of  the week

The WEEKDAY function returns 1 to 7 based on a date's day of the week, the second argument tells the function which day a week begins on. Using 2 as the second argument makes the week start on a Monday.

WEEKDAY(C3,2)

becomes

WEEKDAY(43101,2)

and returns 1.

The number calculated by the WEEKDAY function tells us which day of week a date is:

1 - Monday
2 - Tuesday
3 - Wednesday
4 - Thursday
5 - Friday
6 - Saturday
7 - Sunday

Step 2 - Check if the number is larger than 5

The larger than sign checks if the weekday function is larger than 5. If the number is larger than 5 then it must be a Saturday or Sunday, see list above.

WEEKDAY(C3,2)>5

becomes

1>5 and returns FALSE.

Step 3 - Boolean value determines if the cell is highlighted

If the logical expression returns TRUE then the cell is highlighted and FALSE nothing happens.

6.3 How to highlight record when a date falls on a weekend

Conditional formatting formula applied to cell range B3:D20

This formula is almost as the first formula, except there is a dollar sign in front of the B (column number). This makes the column absolute meaning the cell reference doesn't change when the Conditional Formatting moves to the next column. It only changes when it moves to a new row.

Example,

Cell	Formula
B3	=WEEKDAY($B3,2)>5
C3	=WEEKDAY($B3,2)>5
D3	=WEEKDAY($B3,2)>5
B4	=WEEKDAY($B4,2)>5
C4	=WEEKDAY($B4,2)>5
D4	=WEEKDAY($B4,2)>5

7. How to highlight dates based on day of week

The TEXT function converts a value to text based on formatting code.  The second argument "DDDD" converts the date to day of week.

TEXT(B3,"DDDD")

becomes

TEXT(43101,"DDDD")

and returns Monday.

The equal sign checks if the value is equal to cell $D$3. The dollar signs make sure that cell reference $D$3 doesn't change. Each cell in range B3:B14 is evaluated and if they are equal the logical expression returns a boolean value: TRUE or FALSE.

TEXT(B3,"DDDD")=$D$3

becomes

"Monday"="Monday" and returns TRUE. This highlights cell B3.

7.1 How to apply conditional formatting to a cell range

1. Select cell range.
2. Go to tab "Home"
3. Press with left mouse button on "Conditional Formatting" button.
4. Press with left mouse button on "New Rule..."
   
5. Select "Use a formula to determine which cells to format"
6. Type the formula in field "Format values where this formula is true:"
7. Press with left mouse button on "Format" button, then pick a formatting.
8. Press with left mouse button on OK button.
9. Press with left mouse button on OK button.

7.2 Highlight records based on day of week

Column B contains all the dates we want to be evaluated, we need to make sure that the conditional formatting formula is locked to column B. A dollar sign in front of column B is what we need.

 

8. Highlight current date

Cell range B3:B14 has conditional formatting applied, the formula checks if the date is today.

Conditional formatting formula:

A conditional formatting formula must evaluate to TRUE or FALSE or their equivalents any number or 0 (zero).

The TODAY function returns today's date. The equal sign compares the value to the date in cell B3 and returns TRUE or FALSE.

8.1 How to apply conditional formatting to a cell range

1. Select cell range.
2. Go to tab "Home"
3. Press with left mouse button on "Conditional Formatting" button.
4. Press with left mouse button on "New Rule..."
   
5. Select "Use a formula to determine which cells to format"
6. Type the formula in field "Format values where this formula is true:"
7. Press with left mouse button on "Format" button, then pick a formatting.
8. Press with left mouse button on OK button.
9. Press with left mouse button on OK button.

8.2 Highlight records based on date being today

Conditional formatting formula:

This CF-formula is applied to cell range B3:D12.

9. How to highlight MAX and MIN value within a month

The image above shows rows highlighted based on value in column C being the largest or smallest in that particular month.

Conditional formatting formula (Max)

Conditional formatting formula (Min)

9.1 Explaining CF formula (Max)

Step 1 to 3 make sure that values from the correct year and month are extracted based on the date of the current row.

Step 1 - Compare year to dates

The YEAR function returns the year from an Excel date.

YEAR($B$3:$B$16)= YEAR($B3))

becomes

YEAR({41141; 41139; 41131; 41127; 41124; 41121; 41120; 41117; 41116; 41107; 41103; 41095; 41093; 41117})= YEAR(41141))

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.

Step 2 - Compare month to dates

The MONTH function returns a number representing the month from an Excel date.

MONTH($B$3:$B$16)=MONTH($B3)

becomes

MONTH({41141; 41139; 41131; 41127; 41124; 41121; 41120; 41117; 41116; 41107; 41103; 41095; 41093; 41117})=MONTH(41141)

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.

Step 3 - Multiply arrays

Both conditions must be met in order to extract the number, we apply AND-logic if we multiply the arrays.

(YEAR($B$3:$B$16)= YEAR($B3))* (MONTH($B$3:$B$16)

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

and returns

{1;1;1;1;1;0;0;0;0;0;0;0;0;0}.

Step 4 - Convert boolean values

The IF function returns row number if the logical expression evaluates to TRUE and nothing "" if FALSE.

IF((YEAR($B$3:$B$16)= YEAR($B3))* (MONTH($B$3:$B$16)=MONTH($B3)), $C$3:$C$16, "")

becomes

IF({1;1;1;1;1;0;0;0;0;0;0;0;0;0}, $C$3:$C$16, "")

and returns

{3579; 3082; 6883; 3588; 332; ""; ""; ""; ""; ""; ""; ""; ""; ""}.

Step 5 - Get largest number from array

The MAX function returns the maximum value from a cell range or array.

MAX(IF((YEAR($B$3:$B$16)= YEAR($B3))* (MONTH($B$3:$B$16)=MONTH($B3)), $C$3:$C$16, ""))

becomes

MAX({3579; 3082; 6883; 3588; 332; ""; ""; ""; ""; ""; ""; ""; ""; ""})

and returns 6883.

Step 6 - Compare the largest value to current value

The equal sign compares the values and returns TRUE if they match.

MAX(IF((YEAR($B$3:$B$16)= YEAR($B3))* (MONTH($B$3:$B$16)=MONTH($B3)), $C$3:$C$16, ""))=$C3

becomes

6883=3579

and returns FALSE. Row 3 is not highlighted.

10. Highlight odd/even months

Color odd months

Conditional formatting formula:

10.1 Explaining CF formula in cell B6

Step 1 - Calculate number of month

The MONTH function returns a number representing a month based on a date. 1 = January, 2 = February ... 12 = December.

MONTH($B6)

becomes

MONTH(1/1/2008)

becomes

MONTH(39448)

and returns 1. 1 = January.

Step 2 - Calculate remainder

The MOD function returns the remainder after a number is divided by a divisor.

MOD(MONTH($B6),2)

becomes

MOD(1, 2)

and returns 1.

In cell B10 the date changes to 2/2/2008. MOD(MONTH($B6),2) becomes MOD(2,2) and returns 0 (zero). This cell is not highlighted with this specific color.

10.2 How to apply conditional formatting

1. Select cell range B6:D19
2. Go to tab "Home" on the ribbon
3. Press with mouse on "Conditional Formatting" button
4. Press with mouse on "New Rule.."
5. Select "Use a formula to determine which cells to format"
   
6. Type =MOD(MONTH($B6),2) in field "Format values where this formula is true:"
7. Then press with left mouse button on "Format..." button
   
8. Go to tab "Fill"
9. Pick a color
10. Press with left mouse button on OK button
11. Press with left mouse button on OK button to return to Excel.

10.3 Color even months

Conditional formatting formula:

The NOT function returns the boolean opposite to the given argument. The numerical equivalent to boolean value TRUE is 1 and FALSE is 0 (zero).

10.4 Highlight odd years

Conditional formatting formula:

The YEAR function returns the year based on a date.

10.5 Highlight even years

Conditional formatting formula:

10.6 Line between months - conditional formatting

Conditional formatting formula:

10.7 Line between years - conditional formatting

Conditional formatting formula:

11. Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that overlaps.

The first and fourth record are highlighted because the date ranges overlap. 1/4/2010 - 1/8/2010 overlaps with 1/7/2010 - 1/9/2010.

Here are the steps needed to create a conditional formatting formula

1. Select cell range B6:D12.
2. Press with left mouse button on "Home" tab.
3. Press with left mouse button on "Conditional Formatting" button.
4. Press with left mouse button on "New Rule..".
5. Press with left mouse button on "Use a formula to determine which cells to format".
6. Type =SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12))>1 in "Format values where this formula is TRUE" window.
7. Press with left mouse button on "Format.." button.
8. Press with left mouse button on "Fill" tab.
9. Pick a color. This color is used to highlight records that overlap.
10. Press with left mouse button on "OK".
11. Press with left mouse button on "OK".
12. Press with left mouse button on "OK".

Explaining conditional formatting formula

=SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12))>1

Step 1 - Filter records where ($C6<=$D$6:$D$12) is TRUE

$C6<=$D$6:$D$12 returns an array of TRUE and/or FALSE if date in cell C6 is smaller or equal to each date in $D$6:$D$12.

All dates in$D$6:$D$12 are larger than $C6 so the returning array is (TRUE, TRUE, TRUE,TRUE, TRUE, TRUE,TRUE)

Step 2 - Filter records where ($D6>=$C$6:$C$12) is TRUE

($D6>=$C$6:$C$12) returns an array of TRUE and/or FALSE if date in cell D6 is bigger or equal to each date in $C$6:$C$12.

The returning array is (TRUE, FALSE, FALSE,TRUE, FALSE, FALSE,FALSE)

Step 3 - Putting it all together

($C6<=$D$6:$D$12)* ($D6>=$C$6:$C$12)) returns the following array (1,0,0,1,0,0,0).

That means array number one and four is overlapping C6:D6) but in this case we just want to know if any date range is overlapping.

=SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12)) sums the array (1,0,0,1,0,0,0) and returns 2.

If the formula returns a number bigger than one there is at least one overlapping date range.

=SUMPRODUCT(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12))>1 returns TRUE or FALSE.

Recommended articles

Get excel *.xls file

highlight overlapping dates.xls

12. Highlight records based on overlapping date ranges and a condition

adam asks:

Answer:

The image above demonstrates a conditional formatting formula that highlights records based on a condition (column B) and date ranges (column C and D).

Records on row 3 and 5 are highlighted, they share the same condition in column B and their date ranges overlap.

How to apply conditional formatting formula to the data set

1. Select a cell range
2. Press with left mouse button on the "Home" tab
3. Press with left mouse button on "Conditional Formatting" button
4. Press with left mouse button on "New Rule.."
   
5. Press with left mouse button on "Use a formula to determine which cells to format"
6. Type =SUMPRODUCT(($C3<=$D$3:$D$6)*($D3>=$C$3:$C$6)*($B3=$B$3:$B$6))>1 in "Format values where this formula is TRUE" window.
   
7. Press with left mouse button on "Format.." button
8. Press with left mouse button on "Fill" tab
9. Select a color for highlighting cells.
   
10. Press with left mouse button on "Ok"
11. Press with left mouse button on "Ok"
12. Press with left mouse button on "Ok"

Explaining CF formula in cell B3

The formula is a conditional formatting formula, however, you can enter it in cell E3. Then copy the cell and paste to cells below.

Select cell E3 and then start the "Evaluate Formula" tool on the "Formula" tab on the ribbon.

Press with left mouse button on "Evaluate" button to go through each calculation step.

Step 1 - Check if cells are equal to cell value

The equal sign lets you compare a cell value with a range. If it is the same the logical expression returns TRUE, if not FALSE.

$B3 is locked to column B, however, the row number changes as the CF formula is applied to cells below.

$B3=$B$3:$B$6

becomes

"ABC "={"ABC ";"BCD ";"ABC ";"ABC "}

and returns {TRUE;FALSE;TRUE;TRUE}.

Step 2 - Check if date ranges overlap

The following two logical expressions check which date ranges overlap with the first record. The parenthes make sure that the order of calculation is correct. We want the comparisons to be performed first and then multiply the arrays.

($C3<=$D$3:$D$6)*($D3>=$C$3:$C$6)

becomes

(39814<={40178;40178;40359;39813})* (40178>={39814;39965;39995;39600})

becomes

{TRUE;TRUE;TRUE;FALSE}* {TRUE;TRUE;TRUE;TRUE}

and returns {1;1;1;0}

Step 3 - Multiply arrays

($C3<=$D$3:$D$6)*($D3>=$C$3:$C$6)*($B3=$B$3:$B$6)

becomes

{TRUE;FALSE;TRUE;TRUE}* {1;1;1;0}

and returns {1;0;1;0}

Step 4 - Sum values in array

SUMPRODUCT(($C3<=$D$3:$D$6)*($D3>=$C$3:$C$6)*($B3=$B$3:$B$6))

becomes

SUMPRODUCT({1;0;1;0})

and returns 2.

Step 5 - Check if sum is larger than 1

SUMPRODUCT(($C3<=$D$3:$D$6)*($D3>=$C$3:$C$6)*($B3=$B$3:$B$6))>1

becomes

2>1

and returns TRUE. Cell B3 is highlighted.

Get Excel *.xlsx file

duplicate values and overlapping dates.xlsx

13. Highlight date ranges overlapping selected record - VBA

This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range.

This method will automatically highlight the selected date range and all other overlapping date ranges, press with left mouse button on any another date range to highlight it and it's overlapping date ranges as well.

Event code is basically a VBA macro that is run when an event is triggered, it can be a specific worksheet being activated or a cell being selected among many other things.

The image above shows cell D9 selected, the event code then copies the date range in cell range C9:D9 to cell range F2:G2. A conditional formatting formula uses the values in cells F2 and G2 to highlight other date ranges in the Excel Table that overlaps.

13.1. Create an Excel Table

An Excel Table has many great features, the one we are most interested in for today's tutorial is "structured references". It is basically a cell reference to an Excel Table, however, more advanced but still easy to construct.

This makes it possible to apply conditional formatting to all data in Excel Table even if data is added or deleted.

1. Select any cell in your data set.
2. Press short cut keys CTRL + T and "Create Table" dialog box appears, see image above.
3. Enable checkbox if your table contains header names.
4. Press with left mouse button on OK to apply settings and create Excel Table.

The cell formatting changes to indicate that the data set is now an Excel Table, you have the option to change the Table style if you want.

Go to tab "Table Design" on the ribbon, press with left mouse button on the button shown in the image below to expand the list of Table Styles.

Hover over a table style and your Excel Tables instantly changes to show what it looks like. This makes it easy to preview a table style and pick one that you like in no time.

Back to top

13.2. Event code

The code below must be placed in a worksheet module to work properly, you cant put it in a regular module. This macro is triggered when the user selects any cell in columns C or D.

It copies the selected dates to cell range F2:G2 in order to highlight the appropriate dates using conditional formatting in the next step.

'Event code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'Check if selected cell is in column C or D
    If Not Intersect(Target, Range("C:D")) Is Nothing Then

        'Save dates from column C and D to cell range F2:G2
        Range("F2:G2") = Range("C" & Target.Row & ":D" & Target.Row).Value
    End If
End Sub

Back to top

13.3. Where to put the event code?

The image above shows the Visual Basic Editor, it contains the project explorer to the left and a window to the right showing the VBA code if any.

1. Copy the above event code. (CTRL + c).
2. Press with right mouse button on on sheet name.
3. Press with left mouse button on "View code" and Visual Basic Editor appears with the corresponding worksheet module open.
4. Paste event code, see image above. (CTRL + v).
5. Exit VB Editor and return to Excel.

13.4. Conditional formatting formula

1. Select date ranges in the Excel Table, the image above shows cell range C2:D16 selected.
2. Go to tab "Home" on the ribbon.
3. Press with left mouse button on the "Conditional formatting" button located on the ribbon.
4. Press with left mouse button on "New Rule..".
5. Select Rule Type: "Use a formula to determine which cells to format". See image above.
6. Enter the Conditional Formatting formula shown below these steps.
7. Press with left mouse button on the"Format..." button.
8. Press with left mouse button on tab "Fill".
9. Pick a fill color.
10. Press with left mouse button on Ok.
11. Press with left mouse button on OK

Conditional formatting formula

Back to top

13.4.1 Explaining CF formula

I recommend that you enter the formula in cell H2 to easily see how the CF formula works. Copy cell H2 and paste to cell range H3:H16.

Excel has a built-in tool named "Evaluate Formula" that allows you to see formula calculations for the selected cell step by step.

Go to tab "Formulas" on the ribbon. Press with mouse on the "Evaluate Formula" button. A dialog box appears, see image above.

The "Evaluate" button takes you through the formula calculations step by step, the underlined part of the formula is what will be evaluated next time you press the Evaluate" button.

Italic text shows the result. Press with left mouse button on the "Evaluate" button repeatedly until you are pleased or the formula has been completely calculated. Press with left mouse button on "Close" button to dismiss the dialog box.

I have described each step in the formula calculation entered in cell H2 below. The formula corresponds to the CF formula applied to cell range C2:D2.

Step 1 - Check if date is smaller than selected date in cell F2

The less than sign checks if the date in cell F2 is smaller than the date in cell D2. The result is the boolean values True or False.

Excel handles dates as numbers, however, cell formatting shows them as dates. For example, enter 1 in any cell. Select the cell and press CTRL + 1 to open the "Format cells" dialog box.

Apply any date formatting, press with left mouse button on OK button. The cell now shows 1/1/1900, this proves to you that Excel handles dates as numbers. A date less than another date means it is a smaller number meaning it is earlier than the other date.

Time is the decimal part of a number. 1/24 is an hour.

Cell reference $F$2 is an absolute cell reference meaning it does not change when the cell is copied to cells below, this applies also to Conditional formatting formulas as well.

Cell reference $D2 is only locked to the column and the row number is relative meaning it changes from cell to cell.

$F$2<$D2

becomes

41414.4583333333<41415.9166666667

and returns boolean value True.

Step 2 - Check if date is larger than selected date in cell G2

$G$2>$C2

becomes

41415.9166666667>41414.4583333333

and returns TRUE.

Step 3 - Multiply results

Multiplying boolean values is the same as applying AND-logic. True * True = 1, True * False = 0 and False * False = 0.

The numerical equivalent to True is 1 and False is 0 (zero).

The parentheses allow you to control the calculation, we want to evaluate the less than and larger than signs before we multiply the results.

($F$2<$D2)*($G$2>$C2)

becomes

TRUE * TRUE

and returns TRUE. Cell C2 and cell D2 will be highlighted by the Conditonal Formatting tool.

Back to top

13.5. Sort highlighted values

Finding conditionally formatted values in a large Excel Table is not easy but there is a way that can be useful. This method sorts all conditionally formatted values at the top of the Excel Table.

1. Press with right mouse button on on a cell that is highlighted.
2. Press with mouse on Sort, see image above.
3. Press with mouse on "Put selected cell color on top".

Back to top

13.6. Hide values in cell F2:G2

You can hide the dates if you don't want the selected dates to be shown in cell range F2:G2. The following steps demonstrate how to format cells manually.

 

1. Select cell F2:G2
2. Press with right mouse button on on cells.
3. Press with left mouse button on "Format cells..."
4. Select category:  "Custom"
5. Type ;;;
6. Press with left mouse button on OK

They will hide values in cell range F2:G2, however, they still exist there. You can check that by selecting either cell F2 or G2 and the values show up in the formula bar.

The formula bar shows a number with decimals, this is how Excel handles dates. They are simply numbers formatted as dates, 0 (zero) is 1/1/1900 and 1/1/2000 is 36526.

Time values are decimal numbers. 12:00 A.M. is 0 (zero) and 1/24 is 01:00 A.M. 23/24 is 11:00 P.M.

Combine both date numbers and time numbers and you get 36526.5 equals 1/1/2000 12:00 P.M.

Back to top

Back to top

Recommended reading

Built-in conditional formatting

Data Bars Color scales Icons

Highlight cells rule

Highlight cells containing string
Highlight a date occuring
Conditional Formatting Basics
Highlight unique/duplicates

Top bottom rules

Highlight top 10 values
Highlight top 10 % values
Highlight above average values

Basic CF formulas

Working with Conditional Formatting formulas
Find numbers in close proximity to a given number
Highlight empty cells
Highlight text values

Search using CF

Highlight records – multiple criteria [OR logic]
Highlight records [AND logic]
Highlight records containing text strings (AND Logic)
Highlight lookup values

Unique distinct

How to highlight unique distinct values
Highlight unique values and unique distinct values in a cell range
Highlight unique values in a filtered Excel table
Highlight unique distinct records

Duplicates

How to highlight duplicate values
Highlight duplicates in two columns
Highlight duplicate values in a cell range
Highlight smallest duplicate number
Highlight more than once taken course in any given day
Highlight duplicates with same date, week or month
Highlight duplicate records
Highlight duplicate columns
Highlight duplicates in a filtered Excel Table

Compare

Highlight missing values between to columns
Compare two columns and highlight values in common
Compare two lists of data: Highlight common records
Compare tables: Highlight records not in both tables
How to highlight differences and common values in lists
Compare two columns and highlight differences

Min max

Highlight smallest duplicate number
How to highlight MAX and MIN value based on month
Highlight closest number

Dates

Advanced Date Highlighting Techniques in Excel
How to highlight MAX and MIN value based on month
Highlight odd/even months
Highlight overlapping date ranges using conditional formatting
Highlight records based on overlapping date ranges and a condition
Highlight date ranges overlapping selected record [VBA]
How to highlight weekends [Conditional Formatting]
How to highlight dates based on day of week
Highlight current date

Misc

Highlight every other row
Dynamic formatting
Advanced Techniques for Conditional Formatting
Highlight cells based on ranges
Highlight opposite numbers
Highlight cells based on coordinates

Excel categories

Comments (85)