Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color certain strings of text
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color certain strings of text
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 On Apr 7, 12:55*am, Napoleon Vang wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color certain strings of text
Conditional FormattingCell 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 On Tue, 6 Apr 2010 16:55:01 -0700, Napoleon Vang wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color certain strings of text
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 "Pete_UK" wrote: 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 On Apr 7, 12:55 am, Napoleon Vang wrote: 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? . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color certain strings of text
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 "Napoleon Vang" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking for Text strings | Excel Worksheet Functions | |||
VLookup and text strings | Excel Discussion (Misc queries) | |||
Separating Strings of Text | Excel Discussion (Misc queries) | |||
Sorting different strings of text | New Users to Excel | |||
Text Strings Swap | Excel Worksheet Functions |