Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Make resultant cell blink

0

Hello

i have a workbook with multiple sheets. On another workbook I have a search function.

when i do a search from this workbook it highlights the result in red in the workbook with mutiple sheets.

i am hoping after searching that the resultant cell will flash red three times at intervals of 2 seconds and then the red color disappears. Is there a way to modify the code i am using so this can happen? The VBA code i am using is as follows

Sub FindMe()
   Dim MySearch As Variant
      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
      If InStr(1, ActiveCell.Value, MySearch, vbTextCompare) Then
       ActiveCell.Interior.Color = vbRed
        End
    End If
Next x
MsgBox "Sorry " & MySearch & " was not found in any sheet", vbOKOnly, "No Match!"
End Sub

Thanks for any assistance
excel
posted by: mycobblermends (rep: 6) on Thu Jun 23, 2022 at 7:08 am
Answer
Discuss

Discussion

Yes it's possible to do something like that (at a sheet level- you can't view them all at once easily! ) but please attach a file to your question and I'll try to reply tomorrow.
John_Ru (rep: 6142) Jun 23, '22 at 9:40 am
Add to Discussion

Answers

0
Selected Answer

Hello mycobblermends,

Updated June 24/22 (attached sample file also updated)

This can be achieved with a simple Do Until ... Loop. Modifications to your code are in bold:

Sub FindMe()
   Dim MySearch As Variant
   Dim n As Integer   ' used to count number of loops
   Dim CurCol As Long   ' colour index of existing cell fill colour

      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
      If InStr(1, ActiveCell.Value, MySearch, vbTextCompare) Then
       CurCol = ActiveCell.Interior.Color  ' capture existing cell fill colour
       n = 0
       Do Until n = 3     ' loop 3 times through making cell fill colour red and nothing
        ActiveCell.Interior.Color = vbRed
        Application.Wait (Now + TimeValue("00:00:02"))     ' pause for 2 seconds while red
        ActiveCell.Interior.Color = xlNone      ' remove cell fill colour
        Application.Wait (Now + TimeValue("00:00:02"))     ' pause for 2 seconds with no cell fill colour
         n = n + 1
       Loop     ' after looping 3 times the cell fill will be nothing
       ActiveCell.Interior.Color = CurCol ' return cell fill colour to original
        End
    End If
Next x
MsgBox "Sorry " & MySearch & " was not found in any sheet", vbOKOnly, "No Match!"
End Sub

I've attached a file so you can see how this works (just click the arrow)

Hope this helps.

posted by: WillieD24 (rep: 557) on Thu Jun 23, 2022 at 1:03 pm
Discuss

Discussion

Hello WillieD24
This worked. Thank you
in another workbook I have, there are cells with colors already in them. When i apply the code with the blinking color effects it unforunately removes the color that was in the cell. Is there a way around this. 
Thank you in advance
mycobblermends (rep: 6) Jun 23, '22 at 6:27 pm
@Mycobblermends

Attaching a file to your question may have avoided this isuue but.. 

@Willie

Good answer.

I leave you to show how the colour of the found cell can be recorded and restored after the blink sequence. 

Haven't tried  your file (just on my phone for now) but assume it toggle between cells and sheets if the search text is found in several locations. Might not be an issue here but the SheetSelectionChange event might be an alternative (so all found results blink as the user reviews each sheet) .
John_Ru (rep: 6142) Jun 24, '22 at 9:24 am
Hello mycobblermends,

I have updated my original answer to cature the original cell fill colour and then restore it after the "blinking" is finished. The sample file attached has also been updated so you can see how it works there.

If this solves your quest please mark my answer as selected.

Cheers   ;-)
WillieD24 (rep: 557) Jun 24, '22 at 1:32 pm
Hi WillieD24
Thank you for solving my issues.
Greatly appreciated
mycobblermends (rep: 6) Jun 24, '22 at 11:37 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login