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 20:41 On Wed, 24 Feb 2010 09:23:58 +0800, "ela" <ela(a)yantai.org> wrote: >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 If you want your comparison to be case insensitive, you have to either ensure both sides of your comparison are the same case If Lcase(Mid(.Text, i, 1)) = LCase(s) Then OR you can set Option Compare Text at the beginning of your macro. So far as how the color appears, you'll have to experiment. Or you can try varieties of the colorindex property. --ron
From: ela on 23 Feb 2010 21:15 "Ron Rosenfeld" <ronrosenfeld(a)nospam.org> wrote in message news:ep09o59ffcobnm5vng1chv2e5j0lq681q8(a)4ax.com... > On Wed, 24 Feb 2010 09:23:58 +0800, "ela" <ela(a)yantai.org> wrote: > >>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 > > If you want your comparison to be case insensitive, you have to either > > ensure both sides of your comparison are the same case > > If Lcase(Mid(.Text, i, 1)) = LCase(s) Then > > OR you can set Option Compare Text at the beginning of your macro. > > So far as how the color appears, you'll have to experiment. Or you can > try > varieties of the colorindex property. > --ron > Thanks a lot!! although the macro looks clumsy but it works well now!!! Thanks again, Ron~~~ --Ela
From: Ron Rosenfeld on 23 Feb 2010 22:30
On Wed, 24 Feb 2010 10:15:28 +0800, "ela" <ela(a)yantai.org> wrote: >Thanks a lot!! although the macro looks clumsy but it works well now!!! >Thanks again, Ron~~~ > >--Ela Glad to help. Thanks for the feedback. And when you get a chance, take a look at user forms to make your parameter input a bit easier. --ron |