## 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, click 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.

### 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.

- Select any cell in your data set.
- Press short cut keys CTRL + T and "Create Table" dialog box appears, see image above.
- Enable checkbox if your table contains header names.
- Click 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, click 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.

### 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

### Where to put the 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.

- Copy the above event code. (CTRL + c).
- Right-click on sheet name.
- Click "View code" and Visual Basic Editor appears with the corresponding worksheet module open.
- Paste event code, see image above. (CTRL + v).
- Exit VB Editor and return to Excel.

### Conditional formatting formula

- Select date ranges in the Excel Table, the image above shows cell range C2:D16 selected.
- Go to tab "Home" on the ribbon.
- Click the "Conditional formatting" button located on the ribbon.
- Click "New Rule..".
- Select Rule Type: "
*Use a formula to determine which cells to format".*See image above. - Enter the Conditional Formatting formula shown below these steps.
- Click the"Format..." button.
- Click tab "Fill".
- Pick a fill color.
- Click Ok.
- Click OK

Conditional formatting formula

### 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. Click 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. Click the "Evaluate" button repeatedly until you are pleased or the formula has been completely calculated. Click "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, click 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.

### 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.

- Right-click on a cell that is highlighted.
- Click on Sort, see image above.
- Click on "Put selected cell color on top".

### 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.

- Select cell F2:G2
- Right click on cells.
- Click "Format cells..."
- Select category: "Custom"
- Type ;;;
- Click 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.

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 […]

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 […]

Count Conditionally Formatted cells

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

Open Excel files in a folder [VBA]

This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Split data across multiple sheets [VBA]

In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

How to calculate overlapping time ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]

### 2 Responses to “Highlight date ranges overlapping selected record [VBA]”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Hello,

I have files in different folders and locations that have links to other files. How can I break those links without taking each file and break the links. I wish I could choose for example the path C:\ or D:\ and a VBA code to do the job. Do you think that is possible?

Yoram,

you have mail.