Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

Can i Custom Format so the word Apple will show [Red]?

Posted by Tony on January 11, 2002 12:27 PM
I know that Conditional Formatting will allow me to associate certain colors with certain words but, can Custom Formatting do the same thing? I will use the word Apple for an example. What is custom code to have Apple show [Red] each time it's typed?

Thx.
Tony


Re: Can i Custom Format so the word Apple will show [Red]?

Posted by lenze on January 11, 2002 12:41 PM
Custom Format:
[='Apple'][Red]


Re: Can i Custom Format so the word Apple will show [Red]?

Posted by Tony on January 11, 2002 12:49 PM
Sorry Lenze, that formula makes "all" words show as Red.


Re: Can i Custom Format so the word Apple will show [Red]?

Posted by Jim on January 11, 2002 1:06 PM
Hi Tony,

Try, format>conditional formatting> condition 1
cell value is = to: apple> format>patterns> click
on red > add
Next
Condition 2,
cell value is not equal to apple>format>patterns>
click on black > add

hope this helps,

Jim


Re: Can i Custom Format so the word Apple will show [Red]?

Posted by Tony on January 11, 2002 1:26 PM
Hi Jim,
Unfortunately, i have already used all of my Conditional Formating allowances for my cells. I will "have to" use Custom Formatting

Tony

Hi Tony, Try, format>conditional formatting> condition 1


Re: Can i Custom Format so the word Apple will show [Red]?

Posted by Jim on January 11, 2002 1:33 PM

Hi Tony,

I remember seeing something along these lines posted
a while back, and it you can accomplish what your
trying to do with vba code. If you don't get anymore
repsonces scroll down and you will probably find
your answer.

Good luck,

Jim


Re: Can i Custom Format so the word Apple will show [Red]?

Posted by Barrie Davidson on January 11, 2002 1:51 PM

Tony, you can paste this code in to your worksheet (make whatever changes you require).

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Position As Long

Position = InStr(Target.Value, "apple")
If Position = 0 Then Exit Sub
With Target.Characters(Start:=Position, Length:=5).Font
.ColorIndex = 3
End With
End Sub


Regards,
BarrieBarrie Davidson


Minor correction to posting above

Posted by Barrie Davidson on January 11, 2002 1:57 PM

Tony, use this instead (the first one was case sensitive).

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Position As Long
Dim SearchString As String

SearchString = LCase(Target.Value)
Position = InStr(SearchString, "apple")
If Position = 0 Then Exit Sub
With Target.Characters(Start:=Position, Length:=5).Font
.ColorIndex = 3
End With
End Sub

Regards,
BarrieBarrie Davidson


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.