Color certain strings of text

  • Thread starter Thread starter Napoleon Vang
  • Start date Start date
N

Napoleon Vang

How can I make it so that everytime I type say the phrase "Turned Down" it
appears in red throughout a specific column? And "Booked" would appear in
green, etc. other phrase/color combos also?
 
You can use Conditional Formatting for this - look it up in XL Help,
or post some more details of how your data is organised.

Hope this helps.

Pete
 
Conditional Formatting>Cell Value is: "Turned Down" would be the easiest
without any VBA.

In 2003 you only get 3 conditions per cell.......4 if you count default.

2007 has increased that limit to 64......I think<g>


Gord Dibben MS Excel MVP
 
Hi Pete, I'm already using two of the 3 allowed CF's availible on the spread
sheet. One to alternate the color of the rows, the second to format the
entire row when a certain value appears.

Because of this I'm finding it hard to even overwrite some of the cells to a
different color and or format. Isnt there any way to just set a global if
statement or something? x_x
 
With the advantage of having read your exchanges with Pete_UK and Gord, I'll
offer up a VBA solution. This will extend your 'conditional' formatting for
a column. You can keep your conditional formatting for shading rows as you
now have it.

This is worksheet event code, so to put it into the right place, choose the
worksheet that you need it to work on and right-click its name tab and choose
[View Code] from the popup list. Copy and paste and edit the code as
required. You didn't say which column this needs to happen in, so I just
chose "D" at random. You can add more Case Is = statements as needed for
other phrases, and as I've tried to show, if you have 2 or more phrases that
would share a color, you only need use one Case Is = for all of those.

Just be sure that your phrases after Case Is = "ARE IN ALL CAPS" for this to
work. The way this is set up, it becomes case insensitive, so "Booked" =
"BOOKED" = "booked" etc. because all of those from the worksheet will be
converted to "BOOKED" for testing in the code.

Private Sub Worksheet_Change(ByVal Target As Range)
'set up to only change font color if a change
'takes place in a single cell
'this allows you to [Del] groups if needed
'also ignores entries made in row 1 of the
'column so it won't mess with label text color
'
Const colToMonitor = "D" ' column to keep an eye on
'you can discover the colorindex numbers by
'recording a macro while changing font to the
'desired color and examining the code afterwards
Const myRed = 3
Const myGreen = 10

If Target.Cells.Count = 1 And _
Target.Column = Range(colToMonitor & 1).Column And _
Target.Row > 1 Then
'initially set the font color back to automatic
'so it will look as it should in case it does
'not match any of the key phrases
'remove any leading/trailing white space and
'convert to all UPPERCASE for testing
Select Case UCase(Trim(Target))
Case Is = "TURNED DOWN", "TOSSED OUT"
Target.Font.ColorIndex = myRed

Case Is = "BOOKED"
Target.Font.ColorIndex = myGreen

Case Else ' not a match, reset font color
Target.Font.ColorIndex = xlAutomatic
End Select
End If
End Sub
 
Back
Top