Command button vba code


Hi all. I have created a wordsearch grid 12x12 although I see that altering in time. I have included 26 command buttons A-Z. I hope Excel can work with that many? The aim is simple, I click "A" and all the A's highlight for about 10 seconds and so on through to Z.

To recap, I know "HEMP" is in the grid and I can't see it. I click H to see all the H's. If that still hasn't worked I click H again for a longer look or P to see if that more helpful.

I envisage someone starting me off with the "A" and I can amend the code myself for the other 25 letters, but maybe there's a 'one code fits all' scenario that wii be more efficient???

Thanks for reading this.



Selected Answer

This turned out to be a fun little task that I never expected to do haha. Try this macro out:

Sub highlight_values()
' TeachExcel's Mr. Don haha ;)

Dim foundRange As Range
Dim searchRange As Range
Dim firstAddress

' Range in which to search
Set searchRange = ActiveSheet.Range("A1:C3")

' String to search for
strSearch = "A"

With searchRange

    Set foundRange = .Cells.Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlRows, SearchDirection:=xlNext, MatchCase:=False)

    If Not foundRange Is Nothing Then

        firstAddress = foundRange.Address


            ' Yellow background
            foundRange.Interior.Color = 65535

            Set foundRange = .FindNext(foundRange)

        Loop While Not foundRange Is Nothing And foundRange.Address <> firstAddress

    End If

 End With

' Wait 5 seconds
Application.Wait (Now + TimeValue("0:00:05"))

'No background
searchRange.Interior.Color = xlNone

End Sub

The comments in the code should explain everything but let me know if you have any questions. Also, as it is, the search is NOT case sensitive.

I based this macro off of this one: Macro to Find All text or values in the workbook



Thank you so much Mr Don. That has solved my question perfectly I'm also pleased to hear you enjoyed this project too.

Now to be honest, I have two ongoing projects. They are so similar I thought I could get away with just one question to keep it simple. My other project is on a 'Scrabble' type board where I need to keep triple/double score colors. Is there a line of code I can add, amend or remove so the board returns to its original colors? 

Thank you once again for answering my original question.
MrEMann (rep: 8) Jul 12, '19 at 9:49 pm
I have managed to set up a reset button in my second project. So both are perorming exactly as I imagined. Thank you so much for your efforts, I couldn't have completed them without your help.
MrEMann (rep: 8) Jul 13, '19 at 5:28 pm
Sorry I didn't reply faster, I'm glad you got it working though!)
don (rep: 1745) Jul 14, '19 at 11:37 pm
Add to Discussion

Answer the Question

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