Duplicate Names Between Two Date Ranges

[Viper7513]

Good afternoon all,


I am trying to discover a way of highlighting a name (in this example in Column C) if the person has a task that clashes between dates.
So in the table below, Person A has a task running from 01/01/2022 until 01/03/2022 (ROW 1), but they also have a second task that has a date which falls within this range already (ROW 4 - 27/02/2022 - 01/04/2022) and so therefore, Person A cannot be assigned to a this task. So if they are selected, I need the cell to be highlighted.


Any assistance on this would be greatly appreciated.

	TASK START DATE (Column A)	TASK END DATE (Column B)	NAME (Column C)
ROW 1	01/01/2022	01/03/2022	PERSON A
ROW 2	02/02/2022	13/02/2022	PERSON B
ROW 3	01/02/2022	05/03/2022	PERSON C
ROW 4	27/02/2022	01/04/2022	PERSON A

 

[shknbk2]

If you're okay with VBA macros, here's what I came up with for the worksheet's code. If you don't know where to put it, right-click the worksheet tab and choose View Code. You can change the vbYellow to another color if that's not your choice.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim FoundCell As Range
    Dim LastCell As Range
    Dim FirstAddr As String
    Dim PersonTasks() As Date
    Dim pCount As Integer
    Dim i As Integer
    Dim j As Integer
    Dim TaskConflict As Boolean
    
    'Unhighlight Col C in case selected cell(s) do not create conflict
    With Range("C:C").Cells.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    'Check to see if only 1 cell is selected
    If Target.Cells.Count = 1 Then
        'Is the selected cell in Col C and not blank?
        If Target.Column = 3 And Target.Value <> "" Then
        
            '*************
            '* Find all cells in Col C with person's name
            '*   (based on Chip Pearson's Find code)
            '*************
            Set LastCell = Target
            Set FoundCell = Range("C:C").Find(what:=Target.Value, after:=LastCell)
            pCount = -1
            
            If Not FoundCell Is Nothing Then
                FirstAddr = FoundCell.Address
            End If
            Do Until FoundCell Is Nothing
                Set FoundCell = Range("C:C").FindNext(after:=FoundCell)
                '*************
                '* Fill start and end dates into PersonTasks array for each found cell
                '* to be compared later
                '*************
                pCount = pCount + 1
                ReDim Preserve PersonTasks(1, pCount)
                PersonTasks(0, pCount) = FoundCell.Offset(0, -2).Value
                PersonTasks(1, pCount) = FoundCell.Offset(0, -1).Value
                If FoundCell.Address = FirstAddr Then
                    Exit Do
                End If
            Loop
            
            'Compare the start/end times for each task row to make sure start and end dates
            ' don't come between any other task dates
            For i = 0 To pCount - 1
                For j = 1 To pCount
                    If PersonTasks(0, i) > PersonTasks(0, j) And PersonTasks(0, i) < PersonTasks(1, j) _
                      Or PersonTasks(1, i) > PersonTasks(0, j) And PersonTasks(1, i) < PersonTasks(1, j) Then
                        TaskConflict = True
                        Exit For
                    End If
                Next j
            Next i
            
            'If there is a conflict, then re-find all person's cells and highlight
            If TaskConflict Then
                Set FoundCell = Range("C:C").Find(what:=Target.Value, after:=LastCell)
                Do Until FoundCell Is Nothing
                    Set FoundCell = Range("C:C").FindNext(after:=FoundCell)
                    FoundCell.Interior.Color = vbYellow
                    If FoundCell.Address = FirstAddr Then
                        Exit Do
                    End If
                Loop
            End If
        End If
    End If
End Sub

 

[Fluff]

Another option with conditional formatting.

+Fluff 1.xlsm
	A	B	C
1	TASK START DATE	TASK END DATE	NAME
2	01/01/2022	01/03/2022	PERSON A
3	02/02/2022	13/02/2022	PERSON B
4	01/02/2022	05/03/2022	PERSON C
5	27/02/2022	01/04/2022	PERSON A
6	01/05/2022	01/07/2022	PERSON A
7	01/01/2022	03/02/2022	PERSON C
8
9
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C13	Expression	=OR(COUNTIFS($C$2:$C2,C2,$B$2:$B2,">="&A2,$A$2:$A2,"<"&A2),COUNTIFS($C$2:$C2,$C2,$A$2:$A2,"<="&$B2,$B$2:$B2,">"&$B2))	text	NO