Code guidance please

[David1976]

Hi all,
I found this code after looking around the web. The only code I have added is "If R = Empty Then Exit Sub".


Private Sub Search_Click()
Dim Q As String
Dim T As Worksheet, R As String, S As Range
R = InputBox("Please Enter Colour", "Colour Search")
'--------------------------
'Don't remove this code
If R = Empty Then Exit Sub
'--------------------------
For Each T In ThisWorkbook.Sheets
Set S = T.Cells.Find(R, LookIn:=xlValues)
If Not S Is Nothing Then
Q = S.Address
Do
S.Interior.ColorIndex = 36
Set S = T.Cells.Find(S)
Loop While Q <> S.Address
End If
Next
End Sub

Question:
I want to highlight the "inputbox (R)" and the cells row its found in.

Edit: Current code works well.

[mana]

>Edit: Current code works well.


I can't understand your explanation.
but I think you should use "FindNext".

[David1976]

Hi mana,


I think you should use "FindNext".
My bad, "Find" appears to work just the same as "FindNext". Please explain the difference?

I can't understand your explanation.
Sorry, I will try to explain.

S.Interior.ColorIndex = 36
Set S = T.Cells.Find(S)

So "S" is the range and "T" is the worksheet. How do I get "S.Interior.ColorIndex = 36" to include additional columns?

[mana]

FindNext
https://msdn.microsoft.com/en-us/library/office/ff196143.aspx

EntireRow
https://msdn.microsoft.com/en-us/library/office/ff836836.aspx

[David1976]

Hi mana,

I will try

FindNext
https://msdn.microsoft.com/en-us/lib.../ff196143.aspx (https://msdn.microsoft.com/en-us/library/office/ff196143.aspx)
and

EntireRow
https://msdn.microsoft.com/en-us/lib.../ff836836.aspx (https://msdn.microsoft.com/en-us/library/office/ff836836.aspx)

If enter "red" to "InputBox" and the cell value is already "RED" my search points to the correct cell location.

[mana]

MatchCase:=False
https://msdn.microsoft.com/en-us/library/office/ff839746.aspx

[David1976]

Hi mana,

I found a problem with my code that I didn't expect.

If enter "red" to "InputBox" and the cell value is already "RED" my search points to the correct cell location.
I have discovered that if I enter into the inputbox "re" that the cell that contains "Red" gets highlighted. This problem is out of control at the moment. If I enter "R" to the input box anything that starts with "r" or "R" is also highlighted.

[mana]

LookAt:=xlWhole
https://msdn.microsoft.com/en-us/lib.../ff839746.aspx (https://msdn.microsoft.com/en-us/library/office/ff839746.aspx)

[David1976]

Hi mana,

I finally got the code to work. Thank you for your help.


Private Sub Search_Click()
Dim Q As Variant
Dim T As Worksheet
Dim R As String
Dim S As Range

R = InputBox("Please Enter Colour", "Colour Search")

If R = Empty Then Exit Sub

For Each T In ThisWorkbook.Sheets
Set S = T.Cells.Find(R, Lookat:=xlWhole)

If Not S Is Nothing Then
Q = S.Address
Do
S.Interior.ColorIndex = 36
Set S = T.Cells.FindNext(S)

Loop While Q <> S.Address

End If
Next
End Sub

[mana]

> Dim R As String
> If R = Empty Then Exit Sub


If R = "" Then Exit Sub