If I want to change the contents in the cell

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?

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.

Quote:

---

Originally Posted by DRJ

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.

Give this macro a try.

Code:

---

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

---

thanks a mil

You're Welcome :beerchug:

Take Care

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?

Should work fine.

yup it does. :thumb

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

:bow:

I have been at it for several years now. :)