Macro to tell me when the highest sale happened

[tonywatsonhelp]

Hi Everyone,
In column A i have date of sale
in column B I have time of sale (e.g. 13:00:00)
in column D i have number of sales

in cell G7 I have the highest sale of the week
i'd like a macro that when i click on the cell it tells me when this sale happened
so in simple terms i'd like this,

I click on cell G7,
it takes the value in G7 and looks down ColumnD to try find it,
if it finds it it brings up a message box saying
"Sale Happened on:"
"Then the Date it Happen and the time it happened"
e.g "12/04/2023 at 13:59:00"
please help if you can,
thanks
Tony

 

[Micron]

This assumes your date and time values are text:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range

If Not Target.Address = "$G$7" Then Exit Sub
Set cel = Cells.Find(What:=Range("G7"), After:=Range("D1"), LookAt:=xlWhole, LookIn:=xlFormulas, _
      SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
If Not cel Is Nothing Then
    MsgBox "Sale happened on " & cel.Offset(0, -3) & " at " & cel.Offset(0, -2)
End If
Set cel = Nothing

End Sub

 

[tonywatsonhelp]

thank you Micron
its great
Tony

 

[Micron]

Glad I could help & thanks for the recognition.