VBA Code needed to Find Value in Column, Change Color of Cell if Value Found, Else End

[ARJUNDSOUZA]

Dear Excel & VBA Experts,

I am working on a small project and need to do this routine task of finding if any rooms are booked under the category "T3RRF2" - how do I write the code in VBA for the following (excel file attached - FIND "T3RRF2")

1) Find "T3RRF2" in entire Column D
2) If Column D contains "T3RRF2" then cell color = Orange
3) If Column D contains "T3RRF2" then message box = "T3 Room Booking" with the count of how many bookings exist in the Column D for "T3RRF2"
4) If Column D does not contain any "T3RRF2" then End.

As this goes into 100' of rows normally, want to get this automated.

Looking forward for your assistance.

Thank you in advance for your help with this.

Kind regards
Arjun

 

[Luke M]

Here's your code.

Sub FindAndColor()
    Dim fCell As Range
    Dim firstAdd As String
    Dim myCount As Long
    
        
    'Where are we searching
    With Worksheets("Sheet1").Range("D:D")
        'What are we looking for?
        Set fCell = .Find(what:="T3RRF2", LookIn:=xlValues, lookat:=xlWhole)
        
        If Not fCell Is Nothing Then
            'Keep track of where we started
            firstAdd = fCell.Address
            
            Do
                'Keep a running count
                myCount = myCount + 1
                'Color cell orange
                fCell.Interior.ColorIndex = 44
                
                'Look for another match
                Set fCell = .FindNext(fCell)
            Loop Until fCell.Address = firstAdd
            
            MsgBox "T3 Room Bookings: " & myCount
        End If
    End With
                
End Sub

 

[ARJUNDSOUZA]

This is awesome!! Works exactly as I asked. Thanks Luke Moraga
Yes I wanna buy you a drink! any other option... not on Paypal.

 

[ARJUNDSOUZA]

Sorry, I question - does the MsgBox background color have to be the Standard Grey or can that be changed also - may be to color BLUE

Thanks once again.

 

[Luke M]

For the basic MsgBox, you can't change the color. If you want to build your own UserForm, then you would be able to customize the color. In your case, it wouldn't be too bad since we're just displaying info. Could be a form with caption and button. You'd need to Load the user form, set value of caption, then Show user form. Have your button perform the Unload Me command to close.

For the drink, could do Venmo or Zelle?