Active cell value in other Worksheet selected and marked

[LiNy]

Hello community,

I try to create a macro which helps me find the value of a cell in other open worksheets.
I have the value of the active cell in worksheet "KS" and i want to get the cell with the same value from worksheet "BGF" selected.
If possible both cells get hightlighted with red.

thank you for your help!

 

[CountTepes]

I enjoyed the challenge

This code needs to be put into the code area for the worksheet which has the selected sheets. In your instance sheet "KS"
The "Option Compare Text" ignores differences in the case of text and need to be the before any code.

Select a cell on KS and if it finds every cell on BGF which matches it and highlights them all.

Option Compare Text
Option Base 1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim T As Range
Dim T1 As Range
Dim x As Long
Dim C0() As Variant
Static C1() As Variant
Static C2 As Long
Static oldT As Range
Static oldTarget As Range

If Target.Count = 1 Then
    With Worksheets("BGF").Range("A1:P36")
        If Not oldT Is Nothing Then ' Reset the original colours if they are there
            x = 0
            For Each T In oldT
                x = x + 1
                oldT.Interior.ColorIndex = C1(x)
            Next
            oldTarget.Interior.ColorIndex = C2
        End If
        Set T = .Find(Target)
        If Not T Is Nothing Then
            Set T1 = T
            Startaddress = T.Address
            Set T = .FindNext(T)
            Do Until T.Address = Startaddress
                If Not T Is Nothing Then Set T1 = Union(T1, T)
                Set T = .FindNext(T)
            Loop
            x = 0
            ReDim C0(T1.Count)
            For Each T In T1
                x = x + 1
                C0(x) = T.Interior.ColorIndex
            Next
            C2 = Target.Interior.ColorIndex
            C1 = C0
            T1.Interior.ColorIndex = 3
            Target.Interior.ColorIndex = 3
            Set oldTarget = Target
            Set oldT = T1
        End If
    End With
End If
End Sub