PDA

View Full Version : [SOLVED] If I want to change the contents in the cell



Shaolin
04-20-2005, 07:23 PM
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
04-20-2005, 07:50 PM
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
04-20-2005, 07:52 PM
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
04-20-2005, 07:57 PM
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
04-20-2005, 08:35 PM
thanks a mil

Jacob Hilderbrand
04-20-2005, 08:45 PM
You're Welcome :beerchug:

Take Care

Shaolin
04-21-2005, 06:05 AM
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
04-21-2005, 09:02 AM
Should work fine.

Shaolin
04-21-2005, 09:23 AM
yup it does. :thumb

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

:bow:

Jacob Hilderbrand
04-21-2005, 09:56 AM
I have been at it for several years now. :)