Help with VBA Code

[Regtompj]

I have a VBA code that highlights a name all over the sheet when double clicked. I am currently using it on a worksheet that has some similar names so it's highlighting multiple areas. Is there a way to have it find the exact match and only highlight that?

 

[MrKowz]

Can you please post your existing code?

 

[Sektor]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim cell As Range
    
    Application.ScreenUpdating = False
    
    For Each cell In ActiveSheet.UsedRange
        If (cell = Target) And Not IsEmpty(cell) Then cell.Interior.Color = vbYellow
    Next
    
    Cancel = True
    
End Sub

 

[JackDanIce]

n/a

 

[Regtompj]

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim c As Range
Dim FirstAddress As String
Dim wksh As Worksheet
Static ColIdx
Cancel = True
If IsEmpty(ColIdx) Then ColIdx = 33 Else ColIdx = ColIdx + 1
If ColIdx > 43 Then ColIdx = 33
ActiveCell.Interior.ColorIndex = ColIdx
For Each wksh In ThisWorkbook.Worksheets
With wksh.Range("A5:R95")
Set c = .Find(ActiveCell.Value, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.Interior.ColorIndex = ColIdx
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Next wksh
End Sub[CODE]

 

[Sektor]

Did you try my code?

 

[MrKowz]

Try:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim c As Range
Dim FirstAddress As String
Dim wksh As Worksheet
Static ColIdx
Cancel = True
If IsEmpty(ColIdx) Then ColIdx = 33 Else ColIdx = ColIdx + 1
If ColIdx > 43 Then ColIdx = 33
ActiveCell.Interior.ColorIndex = ColIdx
For Each wksh In ThisWorkbook.Worksheets
    With wksh.Range("A5:R95")
        Set c = .Find(ActiveCell.Value, LookIn:=xlValues, [B][COLOR=red]Lookat:=xlWhole[/COLOR][/B])
        If Not c Is Nothing Then
            FirstAddress = c.Address
            Do
                c.Interior.ColorIndex = ColIdx
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With
Next wksh
End Sub