Active cell value in other Worksheet selected and marked

LiNy

New Member
Joined
Mar 4, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

CountTepes

Active Member
Joined
Nov 8, 2010
Messages
252
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,310
Messages
5,600,879
Members
414,413
Latest member
Sinbin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top