Hi guys
I was very kindly given code on this forum which created a "Search Box" on my spreadsheet.
The one thing that I would like to tweak however if that it finds the word/number that has been inputted in the search box itself (as well as finding the other genuine entries). Is there a way that I can search the whole sheet apart from cell C2 (The search box cell)?
Here is the existing code:
I was very kindly given code on this forum which created a "Search Box" on my spreadsheet.
The one thing that I would like to tweak however if that it finds the word/number that has been inputted in the search box itself (as well as finding the other genuine entries). Is there a way that I can search the whole sheet apart from cell C2 (The search box cell)?
Here is the existing code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim response
Dim c As Range
Dim more As Boolean
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Target.Column = 4 And Target.Row <> 4 Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
Exit Sub
End If
more = True
If Target.Value = "t" Then
Target.Value = Date
End If
If Target.Address = "$H$48" And Target.Value = "Ordered" Then
Range("J48:K48").MergeCells = True
End If
If Target.Address = "$H$48" And Target.Value <> "Ordered" Then
Range("J48:K48").MergeCells = False
End If
If Target.Address = "$C$2" Then
Set c = Cells.Find(What:=Target.Value, LookIn:=xlValues)
If c Is Nothing Then Exit Sub
c.Select
While more = True
response = MsgBox("Find Next?", vbYesNo)
If response = vbYes Then
Set c = Cells.FindNext(c)
c.Select
Else
more = False
End If
Wend
End If
End Sub