Colorize specific text

  • Thread starter Thread starter Toby Weaver
  • Start date Start date
T

Toby Weaver

I want to automatically change different groups of text
to different colors. For example, if I had 3 names. Tom,
Dick and Harry. All of the occurences of Tom would be
red, Dick would be green and Harry would be blue. Any
help would be appreciated.

Toby
 
Format / Conditional Formatting

Cond1 - 'Cell value is' equal to "Tom" - Hit Format button and choose Red font
or background
Hit Add

Cond2 - 'Cell value is' equal to "Dick" - Hit Format button and choose Green
font or background
Hit Add

Cond3 - 'Cell value is' equal to "Harry" - Hit Format button and choose Blue
font or background

Incuding the default colour this gives you 4 options - More than that requires
VBA
 
I have about 20 different names. Can you tell me where I
can go for an example.

Thank you
Toby
 
Hi Toby
try the following event procedure (you have to put it in your worksheet
module - see links below for instructions how to do this). This macro
will change the colors in column A. You just have to add some more case
statements for your other names

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case "Tom": .Interior.ColorIndex = 3
Case "Dick": .Interior.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

-----
also see:
http://www.cpearson.com/excel/events.htm
http://www.mvps.org/dmcritchie/excel/event.htm
for more information about event macros

and
http://www.mvps.org/dmcritchie/excel/colors.htm
for information about the used colorindices
 
Quick example - I've included both the interior colorindex (Background colour)
and the font colour. If you only want one of those then delete all the lines
you don't want (eg delete all oCell.interior.... lines to get rid of background
changes)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case Is = "Tom"
oCell.Interior.ColorIndex = 1
oCell.Font.ColorIndex = 3
Case Is = "Dick"
oCell.Interior.ColorIndex = 4
oCell.Font.ColorIndex = 6
Case Is = "Harry"
oCell.Interior.ColorIndex = 5
oCell.Font.ColorIndex = 9
Case Is = "Fred"
oCell.Interior.ColorIndex = 7
oCell.Font.ColorIndex = 10
Case Else
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub
 
LOL - I forgot the application.events completely, and you are setting it back to
true on error without having gone false to start with :-)

Signing off for a bit now as the wife is making me do a Charity walk
(Aaaaaaghhhh - It's Sunday for Pete's sake!!!) Oh well <g>
 
Oops ( using Frank's error handler :-> ):-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
Application.EnableEvents = False
On Error GoTo CleanUp
For Each oCell In Target
Select Case oCell.Value
Case Is = "Tom"
oCell.Interior.ColorIndex = 1
oCell.Font.ColorIndex = 3
Case Is = "Dick"
oCell.Interior.ColorIndex = 4
oCell.Font.ColorIndex = 6
Case Is = "Harry"
oCell.Interior.ColorIndex = 5
oCell.Font.ColorIndex = 9
Case Is = "Fred"
oCell.Interior.ColorIndex = 7
oCell.Font.ColorIndex = 10
Case Else
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell

CleanUp:
Application.EnableEvents = True
End Sub
 
And use the following in a normal module to give you the colours - Row number is
colour number - posted by Bob phillips IIRC

Sub InteriorColors()
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next
End Sub
 
Hi Ken
in my case a &$&#! copy/paste mistake (I saw it and wanted to delete it
but.....).

Frank
P.S.: have a nice walk <vbg>
 
Nice walk!!!!!!!!! - It was raining and windy and I was pushing littlun in his
buggy, which when covered with a raincover, acts like a sail, which I was
pushing *against* the wind - 5 miles worth in that, and I'm very glad that's
over, and no mistake. <g>

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Frank Kabel said:
Hi Ken
in my case a &$&#! copy/paste mistake (I saw it and wanted to delete it
but.....).

Frank
P.S.: have a nice walk <vbg>
Click to expand...
 
Ken said:
Nice walk!!!!!!!!! - It was raining and windy and I was pushing
littlun in his buggy, which when covered with a raincover, acts like
a sail, which I was pushing *against* the wind - 5 miles worth in
that, and I'm very glad that's over, and no mistake. <g>
Click to expand...

rotflol
sounds like a real happy walk. Hopefully your wife recognizes your good
will <vbg>
have a nice sunday
Frank
 
<g> (And to top it all the kebab shop is closed until 16:00 today, so the kebab
I promised myself when we finished hasn't yet materialised - sob sob - Roll on
16:00!!!)
 
Back
Top