You would need some changes to existing code.
Try this..................
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim Num2 As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("D:D"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "A": Num = 10: Num2 = 2 'green and white
Case Is = "B": Num = 1: Num2 = 6 'black and yellow
Case Is = "C": Num = 5: Num2 = 2 'blue and white
Case Is = "D": Num = 7: Num2 = 1 'magenta and black
Case Is = "E": Num = 45: Num2 = 10 'orange and green
Case Is = "F": Num = 3: Num2 = 34 'red and light turquoise
End Select
'Apply the color
rng.Interior.ColorIndex = Num
rng.Font.ColorIndex = Num2
Next rng
endit:
Application.EnableEvents = True
End Sub
For a list of colorindex numbers see David McRitchie's site
http://www.mvps.org/dmcritchie/excel/colors.htm
Gord
On Fri, 30 Nov 2007 05:33:46 -0800 (PST), Spike9458
wrote:
Hi Gordon,
I'm following along pretty well, am able to get the font and the
cell color to change. I'm new to macros, and don't know how to make it
so that both the font color and the cell color change, like white
font, red background, or green font with yellow background ... can it
be done?
Thanks,
--Jim
On Nov 29, 8:20 pm, Gord Dibben <gorddibbATshawDOTca wrote:
To change font color change to
rng.Font.ColorIndex = Num
Gord
On Thu, 29 Nov 2007 05:13:00 -0800 (PST), Spike9458
wrote:
Hey, this is pretty neat, I'm intrigued and learning a lot here. Now
that I know about changing the cell color, is there a way to change
the font color as opposed to (or in addition to) the cell color?
Thanks,
--Jim
On Nov 28, 5:00 pm, Gord Dibben <gorddibbATshawDOTca wrote:
I like your attitude about the learning adventure. Keep it up.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("D:D"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub
Right-click on your sheet tab and "View Code".
Copy/paste the code into that module.
Adjust Range("D:D") to suit
Gord
On Wed, 28 Nov 2007 11:00:02 -0800, Senie
wrote:
Sure go ahead and give me the information to use sheet event code which you
can supply. It will be a learning adventure for me.
I am also going to check out the website you have listed as well.
Thank you so much!
"Gord Dibben" wrote:
How many letters are you talking about?
Using CF as Max points out you can get 3.
If more you can download Bob Phillips' CFPlus which allows for 30
http://www.xldynamic.com/source/xld.....Download.html
Or use sheet event code which I can supply if you want to go that direction.
Gord Dibben MS Excel MVP
On Wed, 28 Nov 2007 07:58:01 -0800, Senie
wrote:
I am trying to produce a formula as to where I key in a specific letter and
the cell will turn to a specific color. for example: if I type in the Letter
"A" the cell the letter is typed in will turn to the color Light Blue.- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -