If I want to change the contents in the cell

[Shaolin]

If I want to change the contents in a bunch of cells in Column E, only if those cells contain "Menu: TBA Wine:TBA." If it contains the string "Menu: TBA Wine:TBA," how do I change it to "Will receive information soon" in red text?

[Jacob Hilderbrand]

Well you can do this quickly without a macro. Just select column E and press Ctrl + h to bring up the replace dialog. Then just replace one string for the other. Then you can create a conditional format to color those cells red.

[Shaolin]

Well you can do this quickly without a macro. Just select column E and press Ctrl + h to bring up the replace dialog. Then just replace one string for the other. Then you can create a conditional format to color those cells red.

Yeah, I know. I just wanted to know how to do it in code out of curiosity. Nothing pressing.

[Jacob Hilderbrand]

Give this macro a try.

Option Explicit

Sub Macro1()
Dim Cel             As Range
Dim Str             As String
Dim FirstAddress    As String
Dim Rng             As Range
Str = "Menu: TBA Wine:TBA"
    With Range("E:E")
        Set Cel = .Find(What:=Str, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
        If Not Cel Is Nothing Then
            FirstAddress = Cel.Address
            Do
                If Rng Is Nothing Then
                    Set Rng = Cel
                Else
                    Set Rng = Union(Rng, Cel)
                End If
                Set Cel = .FindNext(Cel)
            Loop While Not Cel Is Nothing And Cel.Address <> FirstAddress
            Rng.Value = "Will receive information soon"
            Rng.Font.ColorIndex = 3
        End If
    End With
End Sub

[Shaolin]

thanks a mil

[Jacob Hilderbrand]

You're Welcome

Take Care

[Shaolin]

oh, one more question. If the text in the cell was a link from another spreadsheet that read "Menu: TBA Wine:TBA," and I wanted to change it, would that code work?

[Jacob Hilderbrand]

Should work fine.

[Shaolin]

yup it does.

How long have you been using VBA? I must bow to the king (or queen)! lol!!

[Jacob Hilderbrand]

I have been at it for several years now.