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



"Prohock" wrote in message
...
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

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


"ward376" wrote in message
...
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

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

"Rick Rothstein (MVP - VB)" wrote:

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


"ward376" wrote in message
...
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