The following example shows you how to highlight overlapping ranges.

How it works

  1. Select a date in the table.
  2. Conditional formatting highlight date ranges overlapping the selected record in the table.

highlight overlapping date ranges (vba)

Instructions

VBA Code

  1. Right click on sheet name
  2. Click "View code"
  3. Copy and paste macro
  4. Exit VB Editor
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("C:D")) Is Nothing Then
        Range("F2:G2") = Range("C" & Target.Row & ":D" & Target.Row).Value
    End If
End Sub

Conditional formatting

  1. Select table
  2. Go to tab "Home"
  3. Click "Conditional formatting" button
  4. Click "New Rule.."
  5. Select a rule type: Use a formula to determine which cells to format
  6. Enter conditional formatting formula
  7. Click "Format..." button
  8. Click tab "Fill"
  9. Pick a fill color
  10. Click Ok

Conditional formatting formula

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

Excel defined table

If you convert your data to an excel defined table, you don´t need to expand the conditional formatting every time you add new values.

  1. Select data range
  2. Go to tab "Insert"
  3. Click "Table" button (Ctrl + T)
  4. Click OK

Sort highlighted values

Finding conditional formatted values in large table is not easy but there is a way.

highlight overlapping date ranges (vba)1

You can right click on the selected cell and click "Sort" and then click "Put selected cell color on top".

highlight overlapping date ranges (vba)2

Hide values in cell F2:G2

This is optional.

  1. Select cell F2:G2
  2. Right click on cells.
  3. Click "Format cells..."
  4. Select category:  "Custom"
  5. Type ;;;
  6. Click OK

Download *.xlsm file

Highlight overlapping date ranges (vba).xlsm

Functions in this post:

SUMPRODUCT(array1,array2,array3, ...)

Multiplies corresponding components in the given arrays, and returns the sum of those products.