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.
Table of Contents
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.
- 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.
- 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.
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
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.
- Copy the above event code. (CTRL + c).
- Press with right mouse button on on sheet name.
- Press with left mouse button on "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.
4. 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.
- Press with left mouse button on the "Conditional formatting" button located on the ribbon.
- Press with left mouse button on "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.
- Press with left mouse button on the"Format..." button.
- Press with left mouse button on tab "Fill".
- Pick a fill color.
- Press with left mouse button on Ok.
- Press with left mouse button on OK
Conditional formatting formula
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.
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.
- Press with right mouse button on on a cell that is highlighted.
- Press with mouse on Sort, see image above.
- Press with mouse on "Put selected cell color on top".
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.
- Select cell F2:G2
- Press with right mouse button on on cells.
- Press with left mouse button on "Format cells..."
- Select category: Â "Custom"
- Type ;;;
- 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.
Cf dates category
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Explaining CF formula in cell B6 Step 1 - Calculate number of month The […]
The following conditional formatting formula highlights dates based on day of the week. =TEXT(B3,"DDDD")=$D$3 The TEXT function converts a value […]
Cell range B3:B14 has conditional formatting applied, the formula checks if the date is today. Conditional formatting formula: =TODAY()=B3 A […]
The image above shows conditional formatting applied to cell range C8:C20, it highlights cells containing dates that fall on Saturdays […]
Conditional formatting category
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Highlight […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]
In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]
A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]
In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]
The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this blog article, I will demonstrate basic file copying techniques using VBA (Visual Basic for Applications). I will also […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
Overlapping category
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]
The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries […]
The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]
Excel formula categories
Conditional Formatting categories
Excel categories
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.
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.