CODEWITHSUNDEEP
excelvba
JimBoy
JimBoy

Reputation: 597

Highlight overlapping date ranges individually

I'm trying to highlight overlapping date ranges individually for each person in Excel.

So far I came up with this formula:

enter image description here

=SUMMENPRODUKT(($D3<=$E$3:$E$10000)*($E3>=$D$3:$D$10000))>1

But I have trouble to highlight only those rows where a given person, for example Jeff, has overlapping date ranges.

Upvotes: 0

Views: 1225

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34390

Non-VBA

=AND(COUNTIFS($D$6:$D$12,">="&$C6,$C$6:$C$12,"<="&$D6)>1,$B6="Jeff")

or referring to whole of columns C & D

=AND(COUNTIFS($D:$D,">="&$C6,$C:$C,"<="&$D6)>1,$B6="Jeff")

referring to cells in image and applying conditional formatting to cells A6:D12

(if what you mean is to highlight only Jeff if he has overlapping ranges with anyone)

OR

=COUNTIFS($D$6:$D$12,">="&$C6,$C$6:$C$12,"<="&$D6,$B$6:$B$12,$B6)>1

or referring to whole of columns B, C & D

=COUNTIFS($D:$D,">="&$C6,$C:$C,"<="&$D6,$B:$B,$B6)>1

(if what you mean is to highlight anyone who overlaps with himself)

It's usually better to use COUNTIFS with this kind of problem if possible than an array-type solution using SUMPRODUCT.

Upvotes: 1

Kyrubas
Kyrubas

Reputation: 897

Using VBA

Dim currentcell, considerationcell as Range
For each currentcell in Range("C6:C12")
    For each considerationcell in Range("C6:C12")
        If currentcell.Address=considerationcell.Address Then
            GoTo nextiteration
        End If
        If currentcell >= considerationcell and currentcell <= considerationcell.Offset(0,1) Then
            Range(considerationcell.Offset(0,-2), considerationcell.Offset(0,1)).Interior.Color = RGB(111,111,111)
        End If
        If currentcell.Offset(0,1) >= considerationcell and currentcell.Offset(0,1) <= considerationcell.Offset(0,1) Then
            Range(considerationcell.Offset(0,-2), considerationcell.Offset(0,1)).Interior.Color = RGB(111,111,111)
        End If
nextiteration:
    Next
Next

Upvotes: 1

Related Questions