Formatting text within a cell

January 26, 2011 at 14:47:49
Specs: Windows XP
I have a large spreadsheet with hundreds of cells. No formulas or special formatting, just names and dates and other text. There are certain words that I would like to be able to change the text format of. For instance, I would like to be able to change every occurrence of "dog" to "dog" but make it blue text. I haven't been able to figure out how to do this without changing the color of every word in the cell. I want the rest of the cell to remain unformatted, but change the format of only the text I control+F and Replace All. Is there a way to do this?

Using Excel 2003 with WinXP.


See More: Formatting text within a cell

Report •


#1
January 26, 2011 at 15:14:16
Find & Replace will not change the format of a cell, much less a sub-string within a cell.

The only way I can think of doing it is with a macro.

In its simplest form, something like this should work:

Assume the following values in A1:A4 -

It's a Dog's life
My dog is brown
Nice dog!
Dog Gone It

This code will turn each occurrence of "dog", regardless of case, blue

Sub Color_My_Dog()
'Start loop
 For rw = 1 To 4
'Search for string within each cell
   myStart = InStr(UCase(Range("A" & rw)), UCase("Dog"))
'If string is found, color it Blue
    If myStart > 0 Then
       Range("A" & rw).Characters(Start:=myStart, Length:=3).Font.ColorIndex = 5
    End If
 Next
End Sub

I used the term simplist because I have hardcoded "dog" (and its length, 3) in the code. We could get fancier and have the user input the word and let the code figure out the rest out on its own.

Let me know if this is something you are interested in.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 27, 2011 at 08:37:41
I suspected a macro would be able to do it, but I am not familiar with how to write them. I specifically wanted to change each instance of "DQ(NT)", which is a code we use in my lab, to be colored blue. I have modified the macro you posted to say


Sub Color_My_Dog()
'Start loop
 For rw = 1 To 132
'Search for string within each cell
   myStart = InStr(UCase(Range("A" & rw)), UCase("DQ(NT)"))
'If string is found, color it Blue
    If myStart > 0 Then
       Range("A" & rw).Characters(Start:=myStart, Length:=6).Font.ColorIndex = 5
    End If
 Next
End Sub

Basically I substituted the phrase I wanted for 'dog' and modified the number of letters in that phrase from 3 to 6.

I tested this and it works, however now I need to modify which columns it selects (specifically, I need it to change the information in columns F, G, and H). Is there a way to have it inspect multiple columns as well?

Thank you VERY much by the way. Everyone in my lab swore to me this was not possible. This one macro will be used for lots of projects I am sure.


Report •

#3
January 27, 2011 at 12:40:15
Where ever you see "Range("A" & rw)" it means that the code is referring to Column A.

You could replicate the majority of the code 3 times to refer to Range("F" & rw)" then Range("G" & rw)" then Range("H" & rw)", or you could be a lot more elegant and use another syntax for referencing ranges.

Cells(rw, 1) will also reference Column A since Column A is Column 1.

Cells(rw, 6) would reference Column F, 7 would reference G, etc.

So, let's use that syntax and loop on both the Column and Row numbers:

Sub Color_My_Dog()
'Start loops
 For col = 6 To 8
  For rw = 1 To 132
'Search for string within each cell
   myStart = InStr(UCase(Cells(rw, col)), UCase("DQ(NT)"))
'If string is found, color it Blue
    If myStart > 0 Then
       Cells(rw, col).Characters(Start:=myStart, Length:=6).Font.ColorIndex = 5
    End If
  Next
 Next
End Sub

BTW: The UCase method is only used so that the code won't care about the case of the string it is looking for. The code forces whatever is in the cell it is looking at to be Upper Case - within it's own "memory" - and then compares it to the Upper Case version of the string so that it will always match.

In other words, UCase("Dq(NT)"), UCase("dQ(NT)"), Ucase("dq(nt)) all look like "DQ(NT) to VBA.

If your string really is DQ(NT) in every cell (i.e. all uppercase in every cell) then you don't need the UCase method.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 27, 2011 at 12:52:10
So helpful, thank you!

I would say that you have spoiled me, and now I will be coming here for help every week... but instead I think you might have just convinced me it is well worth my time to learn how to write macros.

Very kind of you to help, thanks again.


Report •

Ask Question