Format duplicate values on multiple sheets

Hello-

I need a macro that can search for duplicates in 4 different worksheets, (Week1, Weel2, Week3, Week4), in cells E2:E30. If there are duplicates, I need the text to be highlighted red. Essentially what I need is conditional formatting based on duplicate values, except I don't think you can conditionally format ranges on different worksheets. Is this possible to do with a macro? Is there an easier way to solve this problem?

Thanks.

dzparker89,

Welcome to the forum. Give the following a try:

Sub HighlightDuplicatesMacro_for_dzparker89()
    
    Const shtNames As String = "Week1,Week2,Week3,Week4"
    Const DupCells As String = "E2:E30"
    
    Dim ws() As String: ws = Split(shtNames, ",")
    Dim i As Integer, j As Integer
    Dim CheckCell As Range, rngFound As Range
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Reset all cells in order to:                                     '
    '   Allow new duplicates to be found                              '
    '   Have cells that are no longer duplicates to not be highlighted'
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    For i = 0 To UBound(ws)
        Sheets(ws(i)).Range(DupCells).Interior.ColorIndex = 0
    Next i
    
    '''''''''''''''''''''''''''''''
    'Highlight all duplicate cells'
    '''''''''''''''''''''''''''''''
    For i = 0 To UBound(ws)
        For Each CheckCell In Sheets(ws(i)).Range(DupCells)
            If CheckCell.Interior.ColorIndex <> 3 Then
                For j = 0 To UBound(ws)
                    For Each rngFound In Sheets(ws(j)).Range(DupCells)
                        If j = i Then
                            If CheckCell.Address <> rngFound.Address _
                            And LCase(Trim(rngFound.Value)) = LCase(Trim(CheckCell.Value)) Then
                                CheckCell.Interior.ColorIndex = 3
                                rngFound.Interior.ColorIndex = 3
                            End If
                        ElseIf LCase(Trim(rngFound.Value)) = LCase(Trim(CheckCell.Value)) Then
                            CheckCell.Interior.ColorIndex = 3
                            rngFound.Interior.ColorIndex = 3
                        End If
                    Next rngFound
                Next j
            End If
        Next CheckCell
    Next i
    
End Sub

Hope that helps,
~tigeravatar