replace a single character WITH FORMAT in a cell
in [Functions]
|
Prev: Counting NON-Blank fields
Next: Countif Limitations
From: ela on 22 Feb 2010 21:01 if i have a cell as "I am a boy" and I would like to replace all the a's in this cell with red a's How can i achieve that? it seems Excel will replace the whole cell content into red...
From: Ron Rosenfeld on 22 Feb 2010 21:45 On Tue, 23 Feb 2010 10:01:27 +0800, "ela" <ela(a)yantai.org> wrote: >if i have a cell as "I am a boy" > >and I would like to replace all the a's in this cell with red a's > >How can i achieve that? it seems Excel will replace the whole cell content >into red... > First of all, the text was be in the cell as text, and not as a result of a formula. 1. Select the cell. Then in the function bar, manually select each "a" individually, and format the color. 2. Use a VBA Macro to do the same thing. Here's one example: To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), first select the cells you wish to process. Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>. ============================= Option Explicit Sub RedLetter() Dim s As String * 1 Dim c As Range Dim i As Long s = InputBox("Which letter to redden?") If s Like "[!A-Za-z]" Then MsgBox ("Must specify a LETTER") Exit Sub End If For Each c In Selection With c .Value = .Text .Font.ColorIndex = xlAutomatic .Font.TintAndShade = 0 For i = 1 To Len(.Text) If Mid(.Text, i, 1) = s Then .Characters(i, 1).Font.Color = vbRed End If Next i End With Next c End Sub =============================== --ron
From: L. Howard Kittle on 22 Feb 2010 23:17 Hi Ron, Pretty neat lil macro...! Two questions please, I have Option Explicit active. Dim s As String * 1 Why the... String * 1 And with this line of code ..Font.TintAndShade = 0 I have to edit out or I get an error: Run-time 438 Object doesn't support this property or method. VBA help says the TintAndShade value can be a -1 or 1 for light to dark. Not a major issue in my world but just curious. Regards, Howard "ela" <ela(a)yantai.org> wrote in message news:hlvctm$ep4$1(a)ijustice.itsc.cuhk.edu.hk... > if i have a cell as "I am a boy" > > and I would like to replace all the a's in this cell with red a's > > How can i achieve that? it seems Excel will replace the whole cell content > into red... >
From: L. Howard Kittle on 23 Feb 2010 03:00 Hi Ron, Pretty neat lil macro...! Two questions please, I have Option Explicit active. Dim s As String * 1 Why the... String * 1 And with this line of code ..Font.TintAndShade = 0 I have to edit out or I get an error: Run-time 438 Object doesn't support this property or method. VBA help says the TintAndShade value can be a -1 or 1 for light to dark. Not a major issue in my world but just curious. Regards, Howard "ela" <ela(a)yantai.org> wrote in message news:hlvctm$ep4$1(a)ijustice.itsc.cuhk.edu.hk... > if i have a cell as "I am a boy" > > and I would like to replace all the a's in this cell with red a's > > How can i achieve that? it seems Excel will replace the whole cell content > into red... >
From: ela on 23 Feb 2010 03:43
"Ron Rosenfeld" <ronrosenfeld(a)nospam.org> wrote in message > > s = InputBox("Which letter to redden?") > > If s Like "[!A-Za-z]" Then > MsgBox ("Must specify a LETTER") > Exit Sub > End If > I tried to modify your code to handle multiple color replacement but failed, as I found variable s only appears once. Sorry for never writing macro before, would you please kindly show one more line, say, replacing for yellow color? I guess from the extra line I can do it for remaining (e.g. grey, brown etc. to replace words like "boy", "girl" etc) |