Tonysdilemma
New Member
- Joined
- Aug 2, 2020
- Messages
- 30
- Office Version
- 2016
- Platform
- Windows
Hi
Have a workbook with many sheets.
Have VBA to search through sheets and get result. Was hoping to get result to flash 3 times for 3 seconds.
However get the following error; Compile error: Next Without Error. Can someone help? Below is the code. The Green Text in the code is where I believe the error is occuring
Have a workbook with many sheets.
Have VBA to search through sheets and get result. Was hoping to get result to flash 3 times for 3 seconds.
However get the following error; Compile error: Next Without Error. Can someone help? Below is the code. The Green Text in the code is where I believe the error is occuring
VBA Code:
Sub FindMe()
Dim MySearch As Variant
Dim X As Integer
MySearch = InputBox("Please enter a value below, and hit OK to search", _
"What are you looking for?")
Workbooks("brutus.xlsm").Activate
Range("A1").Select
For X = 1 To Sheets.Count
On Error Resume Next
Sheets(X).Select
Cells.Find(What:=MySearch, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
[COLOR=rgb(247, 218, 100)] [/COLOR][COLOR=rgb(65, 168, 95)] If InStr(1, ActiveCell.Value, MySearch, vbTextCompare) Then
Dim d As Date
d = Now
Application.OnTime d + TimeValue("00:00:03"), "ChangeCellColor"
i = i + 1
Range(ActiveCell).Interior.ColorIndex = Choose(i, 3, 0, 3, 0, 3, 0)
If i = 6 Then
i = 0
Application.OnTime d + TimeValue("00:00:03"), "ChangeCellColor", , False
End
End If
Next X
MsgBox "Sorry " & MySearch & " was not found in any sheet", vbOKOnly, "No Match!"
End Sub[/COLOR]