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/lib.../ff196143.aspx

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

[David1976]

Hi mana,

I will try

FindNext
https://msdn.microsoft.com/en-us/lib.../ff196143.aspx
and

EntireRow
https://msdn.microsoft.com/en-us/lib.../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/lib.../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

[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