replace a single character WITH FORMAT in a cell
in [Functions]
|
Prev: Counting NON-Blank fields
Next: Countif Limitations
From: Ron Rosenfeld on 23 Feb 2010 07:00 On Mon, 22 Feb 2010 20:17:43 -0800, "L. Howard Kittle" <lhkittle(a)comcast.net> wrote: >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... >> > Thanks. String * 1 declares a String constant with a length of one. Look up fixed-length strings. If you try to enter a longer string, s will return just the first letter. And the OP wrote he wanted to change "single" character. With regard to TintAndShade, that was added in 2007, and can be a value *BETWEEN* -1 and +1. --ron
From: Ron Rosenfeld on 23 Feb 2010 07:12 On Tue, 23 Feb 2010 16:43:56 +0800, "ela" <ela(a)yantai.org> wrote: > >"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) > Hi Ela, With macros, you learn by doing. But you will find that you need to be VERY specific in what you want to do. For example, in your request, you wrote you wanted to act on a *SINGLE* character, so that is what the macro does. As a matter of fact, if you input multiple characters at the input box, it will only use the first character. You could change the String variable to be variable length, but in the macro it is set to a length of "1". Of course, when you cycle through the string, you need to change the length of the fragment you are looking at to match the length of your input string. You did NOT indicate what you wanted to do if the cell was processed a second time; so I reset the colors back to some nominal value (black) each time the macro is called. So this macro will NOT do multiple colors. But you could easily remove the lines that "reset the color" if that is not what you want. These are things you need to think about before coding. If you are going to replace multiple letters or short strings with different colors, one important consideration will be how to get that information into the macro. I suppose you could have a series of Macros for the different colors, and cycle through a bunch of Input boxes; but this would be rather inefficient. A better choice would be to set up a UserForm, where you could select letters and/or strings; along with corresponding colors. You need to decide how you want that to look, and work. --ron
From: ela on 23 Feb 2010 10:26 "Ron Rosenfeld" <ronrosenfeld(a)nospam.org> wrote in message news:6tg7o5d63iuuu04p7p62op4pepdo3emjhj(a)4ax.com... > Hi Ela, > > With macros, you learn by doing. > > But you will find that you need to be VERY specific in what you want to > do. > > For example, in your request, you wrote you wanted to act on a *SINGLE* > character, so that is what the macro does. As a matter of fact, if you > input > multiple characters at the input box, it will only use the first > character. You > could change the String variable to be variable length, but in the macro > it is > set to a length of "1". Of course, when you cycle through the string, you > need > to change the length of the fragment you are looking at to match the > length of > your input string. > > You did NOT indicate what you wanted to do if the cell was processed a > second > time; so I reset the colors back to some nominal value (black) each time > the > macro is called. So this macro will NOT do multiple colors. But you > could > easily remove the lines that "reset the color" if that is not what you > want. > > These are things you need to think about before coding. > > If you are going to replace multiple letters or short strings with > different > colors, one important consideration will be how to get that information > into > the macro. I suppose you could have a series of Macros for the different > colors, and cycle through a bunch of Input boxes; but this would be rather > inefficient. > > A better choice would be to set up a UserForm, where you could select > letters > and/or strings; along with corresponding colors. You need to decide how > you > want that to look, and work. > > --ron > Hi Ron, I comment two lines (should deal with marker background and color tuning?) and still the codes convert all characters to green, would you please kindly tell me which line is to reset? Sorry for asking for your help but I really only need this for once. ==Ela Option Explicit Sub RedLetter() Dim a As String * 1 Dim t As String * 1 Dim c As String * 1 Dim g As String * 1 Dim ch As Range Dim i As Long a = InputBox("Which letter to redden?") If a Like "[!A-Za-z]" Then MsgBox ("Must specify a LETTER") Exit Sub End If For Each ch In Selection With ch .Value = .Text ' .Font.ColorIndex = xlAutomatic ' .Font.TintAndShade = 0 For i = 1 To Len(.Text) If Mid(.Text, i, 1) = a Then .Characters(i, 1).Font.Color = vbRed End If Next i End With Next ch t = InputBox("Which letter to green?") If t Like "[!A-Za-z]" Then MsgBox ("Must specify a LETTER") Exit Sub End If For Each ch In Selection With ch .Value = .Text ' .Font.ColorIndex = xlAutomatic ' .Font.TintAndShade = 0 For i = 1 To Len(.Text) If Mid(.Text, i, 1) = t Then .Characters(i, 1).Font.Color = vbGreen End If Next i End With Next ch End Sub
From: Ron Rosenfeld on 23 Feb 2010 16:40 On Tue, 23 Feb 2010 23:26:08 +0800, "ela" <ela(a)yantai.org> wrote: >I comment two lines (should deal with marker background and color tuning?) >and still the codes convert all characters to green, would you please kindly >tell me which line is to reset? Sorry for asking for your help but I really >only need this for once. ==Ela > > Option Explicit >Sub RedLetter() > Dim a As String * 1 > Dim t As String * 1 > Dim c As String * 1 > Dim g As String * 1 > Dim ch As Range > Dim i As Long > > a = InputBox("Which letter to redden?") > > If a Like "[!A-Za-z]" Then > MsgBox ("Must specify a LETTER") > Exit Sub > End If > > For Each ch In Selection > With ch > .Value = .Text >' .Font.ColorIndex = xlAutomatic >' .Font.TintAndShade = 0 >For i = 1 To Len(.Text) > If Mid(.Text, i, 1) = a Then > .Characters(i, 1).Font.Color = vbRed > End If > Next i > End With > Next ch > > t = InputBox("Which letter to green?") > > If t Like "[!A-Za-z]" Then > MsgBox ("Must specify a LETTER") > Exit Sub > End If > > For Each ch In Selection > With ch > .Value = .Text >' .Font.ColorIndex = xlAutomatic >' .Font.TintAndShade = 0 >For i = 1 To Len(.Text) > If Mid(.Text, i, 1) = t Then > .Characters(i, 1).Font.Color = vbGreen > End If > Next i > End With > Next ch > End Sub If what you are wanting to do is preserve the color through different iterations, then look at this modification of my code for hints. You need to not only NOT reset the color and tint, but also, note that if you are always executing .value = .text, the replacement will have the color of the first letter. So that has to be done more judiciously. ========================= 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 If .HasFormula Then .Value = .Text 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: ela on 23 Feb 2010 20:23
"Ron Rosenfeld" <ronrosenfeld(a)nospam.org> wrote in message news:cii8o51c8o32f3runpeoitbft1b7u980f1(a)4ax.com... > If what you are wanting to do is preserve the color through different > iterations, then look at this modification of my code for hints. > > You need to not only NOT reset the color and tint, but also, note that if > you > are always executing .value = .text, the replacement will have the color > of the > first letter. So that has to be done more judiciously. > > ========================= > 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 > If .HasFormula Then .Value = .Text > 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 I repeat the Sub several times to test, but the results were unexpected. First, I use LCase so to make the search case-insensitive but failed. No matter I input an upper "K" or the lower one "k", it only replaces the lower "k" for me. Second, I found the yellow color too bright to visualize and so re-used .Font.TintAndShade (I also tried negative values). Again, the color was still very bright. I'm using Excel2007 and so expect the color range should be very wide... 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 If .HasFormula Then .Value = .Text .Font.TintAndShade = 0.5 For i = 1 To Len(.Text) If Mid(.Text, i, 1) = LCase(s) Then .Characters(i, 1).Font.Color = vbRed End If Next i End With Next c End Sub Sub YellowLetter() Dim s As String * 1 Dim c As Range Dim i As Long s = InputBox("Which letter to yellow?") If s Like "[!A-Za-z]" Then MsgBox ("Must specify a LETTER") Exit Sub End If For Each c In Selection With c If .HasFormula Then .Value = .Text .Font.TintAndShade = 0.8 For i = 1 To Len(.Text) If Mid(.Text, i, 1) = LCase(s) Then .Characters(i, 1).Font.Color = vbYellow End If Next i End With Next c End Sub |