Find Macro

  • Thread starter Thread starter Prohock
  • Start date Start date
P

Prohock

I would like to be able to click on a cell and a macro would run that would
highlight all cells in the spreadsheet that have the same value as the cell
that was just clicked. For example if cell A12 has the value "Bob" then if I
left mouse click that cell, all other cells containing the value "Bob" would
be highlighted red. If I click on the cell A13 that has the value "jill" then
all instences of Bob would be unhighlighted and all cells containing the
value Jill would be then highlighted red. I would like this marco function to
work for any cell that is clicked in the A12 to A24 range. The search area
would be A1:A10. Thanks
 
I don't think it would be too easy to set up a trap for individual mouse
clicks on the worksheet... how about an alternative? The following code will
allow you do double-click on any cell in A12:A24 and it will highlight all
the cells with matching text in A1:A10...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim C As Range
Dim SourceRange As Range
Dim SearchRange As Range
Set SourceRange = Range("A12:A24")
Set SearchRange = Range("A1:A12")
If Not Intersect(Target, SourceRange) Is Nothing Then
Cancel = True
SearchRange.Interior.ColorIndex = xlNone
For Each C In SearchRange
If C.Value = Target.Value Then
C.Interior.ColorIndex = 3
End If
Next
End If
End Sub

To implement this code, right-click on the tab for the worksheet you want
this to apply to and copy/paste the above code into the code window that
appeared. Then, go back to the worksheet and double click a name in the
range A12:A24 and watch what happens in the range A1:A10.

Rick
 
Or use the selection change event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim C As Range
Dim SourceRange As Range
Dim SearchRange As Range
Set SourceRange = Range("A12:A24")
Set SearchRange = Range("A1:A10")
If Not Intersect(Target, SourceRange) Is Nothing Then
SearchRange.Interior.ColorIndex = xlNone
For Each C In SearchRange
If C.Value = Target.Value Then
C.Interior.ColorIndex = 3
End If
Next
End If

End Sub

Cliff Edwards
 
Interesting... I could have sworn the OP said right mouse click in his post
which is why I did not suggest the SelectionChange event (figuring he wanted
to be able to click in the cell without triggering the macro)... but I just
re-read his post after seeing your posting... and he did not say "right"
mouse click... he said **left** mouse click... so SelectionChange is
definitely the event to use.

Rick
 
Thanks to both of you, the macro does exactly what I would like it to do, you
people are brilliant!
 
Back
Top