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.

If not more than 3 colours, try conditional formatting

Example:
Select col A, click Format Conditional Formatting
Apply the following using "Formula Is:"
Cond1: =A1="A"
Format to taste

Cond2: =A1="B"
Format to taste

Cond3: =A1="C"
Format to taste
Ok out

Test it out by inputting the 3 letters* (A, B, C) in any cell in col A, the
corresponding format will be triggered.
*non case sensitive
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"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.

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.

It worked when I selected one condition but when I went to the second
condition it did not worked and I tried mulitples ways. However, I did learn
something new.

"Max" wrote:

If not more than 3 colours, try conditional formatting

Example:
Select col A, click Format Conditional Formatting
Apply the following using "Formula Is:"
Cond1: =A1="A"
Format to taste

Cond2: =A1="B"
Format to taste

Cond3: =A1="C"
Format to taste
Ok out

Test it out by inputting the 3 letters* (A, B, C) in any cell in col A, the
corresponding format will be triggered.
*non case sensitive
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"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.

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.

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.

It should work fine, Senie

Try this link to a quick sample with all 3 conditions implemented
http://www.freefilehosting.net/download/NDIzNDM=
senie_CF.xls
(contains a screenshot of the CF dialog)

Copy n paste the entire link into your browser address (including the "=" at
the end). Do not click direct on the link if reading this from MS' webpage.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Senie" wrote in message
...
It worked when I selected one condition but when I went to the second
condition it did not worked and I tried mulitples ways. However, I did
learn
something new.

It did work, YES! I was using "Cell Value Is" instead of "Formula Is".
Thanks a bunch!

"Max" wrote:

If not more than 3 colours, try conditional formatting

Example:
Select col A, click Format Conditional Formatting
Apply the following using "Formula Is:"
Cond1: =A1="A"
Format to taste

Cond2: =A1="B"
Format to taste

Cond3: =A1="C"
Format to taste
Ok out

Test it out by inputting the 3 letters* (A, B, C) in any cell in col A, the
corresponding format will be triggered.
*non case sensitive
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"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.

It worked, YES! Are there other colors available? These colors are dark.

"Gord Dibben" 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.

Those are 6 out of the 56 default colors available in Excel

There are lighter colors.

To see a list of the colors and index numbers visit David McRitchie's site

http://www.mvps.org/dmcritchie/excel/colors.htm


Gord

On Wed, 28 Nov 2007 16:54:01 -0800, Senie
wrote:

It worked, YES! Are there other colors available? These colors are dark.

"Gord Dibben" 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.

Glad you got it up!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Senie" wrote in message
...
It did work, YES! I was using "Cell Value Is" instead of "Formula Is".
Thanks a bunch!

Thanks once again. You have been such a GREAT help!

"Gord Dibben" wrote:

Those are 6 out of the 56 default colors available in Excel

There are lighter colors.

To see a list of the colors and index numbers visit David McRitchie's site

http://www.mvps.org/dmcritchie/excel/colors.htm


Gord

On Wed, 28 Nov 2007 16:54:01 -0800, Senie
wrote:

It worked, YES! Are there other colors available? These colors are dark.

"Gord Dibben" 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.

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 -

The colors worked fine on a new worksheet. However, on the worksheet I need
this code on is not working. However, I do have formulas applied to the cells
that the code would have to be used in. Is it possible that that could be the
problem? I have been working at this since 8:30am Eastern time. HELP PlZ.

"Gord Dibben" wrote:

Those are 6 out of the 56 default colors available in Excel

There are lighter colors.

To see a list of the colors and index numbers visit David McRitchie's site

http://www.mvps.org/dmcritchie/excel/colors.htm


Gord

On Wed, 28 Nov 2007 16:54:01 -0800, Senie
wrote:

It worked, YES! Are there other colors available? These colors are dark.

"Gord Dibben" 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.

The code given was worksheet_change event code.

A change due to a formula will not trigger the code, only hard typing or a cell
edit will trigger.

If your formulas return a letter you should be able to make this work

Tested with formula in A1 =D1 and copied down.

Enter A through F in column D and colors follow in A1:A100

Private Sub Worksheet_Calculate()
Dim Target As Range
For Each Target In Me.Range("A1:A100")
With Target
Select Case UCase(Target.Value)
Case Is = "A": .Interior.ColorIndex = 7
Case Is = "B": .Interior.ColorIndex = 10
Case Is = "C": .Interior.ColorIndex = 16
Case Is = "D": .Interior.ColorIndex = 4
Case Is = "E": .Interior.ColorIndex = 6
Case Is = "F": .Interior.ColorIndex = 3
'etc.
End Select
End With
Next Target
End Sub


Gord


On Thu, 29 Nov 2007 06:50:00 -0800, Senie
wrote:

The colors worked fine on a new worksheet. However, on the worksheet I need
this code on is not working. However, I do have formulas applied to the cells
that the code would have to be used in. Is it possible that that could be the
problem? I have been working at this since 8:30am Eastern time. HELP PlZ.

"Gord Dibben" wrote:

Those are 6 out of the 56 default colors available in Excel

There are lighter colors.

To see a list of the colors and index numbers visit David McRitchie's site

http://www.mvps.org/dmcritchie/excel/colors.htm


Gord

On Wed, 28 Nov 2007 16:54:01 -0800, Senie
wrote:

It worked, YES! Are there other colors available? These colors are dark.

"Gord Dibben" 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.

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 -

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 -

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 -

On Nov 30, 1:05 pm, Gord Dibben <gorddibbATshawDOTca wrote:
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 -- Hide quoted text -

- Show quoted text -

Most Excellent! That's what I meant, and it works perfectly.

Thanks for your time and skill with macros!

--Jim