Find a copied cell value

[Excel Newbie7]

Hello All,

I have a problem that I just can't seem to figure out.

After using a vlookup formula, and a if then statment I found out that there are duplicates on my other sheets. I want to use a Macro to use the duplicate value in search and find to locate the item and highlight it on my other sheet. I have searched the web but can't find anything on this.

I can't seem to paste my coppied cell into the what in the find.


Sub Macro15()
'
' Macro15 Macro
'
'
Selection.Copy
Sheets("BAC Box").Select
Cells.Find(What:="Selection.paste", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub



Thanks you,

 

[jsotola]

Selection.paste is a command to paste the contents of the clipboard into the selected worksheet cell

it does not provide a value to the vba macro

you need to retrieve the value of the selected cell

Sub Macro15()
    
[COLOR=#ff0000]    Dim tmp As Variant[/COLOR]
    
    [COLOR=#ff0000]tmp = [/COLOR]Selection[COLOR=#ff0000].Value[/COLOR]
    
    Sheets("BAC Box").Select
    
    Cells.Find(What:=[COLOR=#ff0000]tmp[/COLOR], After:=ActiveCell, LookIn:=xlFormulas, _
               LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
               MatchCase:=False, SearchFormat:=False).Activate
    
    Application.CutCopyMode = False
    
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With


End Sub

 

[Excel Newbie7]

thank you