View Full Version : Code guidance please
David1976
12-18-2016, 03:12 AM
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.
>Edit: Current code works well.
I can't understand your explanation.
but I think you should use "FindNext".
David1976
12-18-2016, 05:10 AM
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?
FindNext
https://msdn.microsoft.com/en-us/library/office/ff196143.aspx
EntireRow
https://msdn.microsoft.com/en-us/library/office/ff836836.aspx
David1976
12-18-2016, 06:24 AM
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.
MatchCase:=False
https://msdn.microsoft.com/en-us/library/office/ff839746.aspx
David1976
12-19-2016, 01:18 AM
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.
LookAt:=xlWhole
https://msdn.microsoft.com/en-us/lib.../ff839746.aspx (https://msdn.microsoft.com/en-us/library/office/ff839746.aspx)
David1976
12-19-2016, 05:15 PM
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
> Dim R As String
> If R = Empty Then Exit Sub
If R = "" Then Exit Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.