Here is what I am looking to do. I need to make this macro where, when I
click on a cell in column B on sheet one, it would copy the contents of that
cell, and insert the value into sheet two in Range("B4"), then call the macro
Searchforstring. How can I do this? Here is what I have so far...

Sub SearchandFind()

On Cell Click

ActiveCell.Copy
Sheets("Sheet2").Activate
Range("B4").PasteSpecial xlPasteValues

Call SearchForString

End Sub

Obviously the "On Cell Click" is incorrect. Does anyone have any possible
idea as to how I can complete this? Thank you for any help that you can be!

In case anyone is wondering or want to see the SearchForString vba (for
incorporation into this macro, or to tell me how inefficient this macro is,
or suggestions on another way to accomplish this):

Sub SearchForString()

Profit = Range("B4")

Sheets("Sheet2").Select
Range("A2:K100").ClearContents

Sheets("$1k Detail").Select

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 2
LSearchRow = 2

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

While Len(Range("B" & CStr(LSearchRow)).Value) 0

'If value in column B = Range "Profit", copy entire row to Sheet1
If Range("B" & CStr(LSearchRow)).Value = Profit Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet2 in next row
Sheets("Sheet2").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("$1k Detail").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Sheets("Sheet2").Select
Range("A2").Select

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub


THANK YOU!