Adjust the "A" and "B" etc. to suit.

Option Compare Text
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("B2"))
'Set vRngInput = Intersect(Target, Range("B2:B10")) for larger range of cells
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case 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

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the above into that module.


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 13:28:01 -0800, Grd wrote:

Hi everyone,

I need to change the color of a cell based on the value the user types in
the cell.
In this cell B2. If they type ON then it should be blue, BC red, AL yellow,
NS green and PEI pink. I can't use conditional formatting from the menu
because its only got three conditions I could be using many more.

Also I don't know how code can run automatically when a person changes the
value of a cell in a sheet.

Could anyone help

Thanks

Suzanne

Thanks Gord,

Copied and pasted and works 'out of the box'. Just changed the "A" and "B"
bits to what I need.

Great work

S

"Gord Dibben" wrote:

Adjust the "A" and "B" etc. to suit.

Option Compare Text
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("B2"))
'Set vRngInput = Intersect(Target, Range("B2:B10")) for larger range of cells
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case 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

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the above into that module.


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 13:28:01 -0800, Grd wrote:

Hi everyone,

I need to change the color of a cell based on the value the user types in
the cell.
In this cell B2. If they type ON then it should be blue, BC red, AL yellow,
NS green and PEI pink. I can't use conditional formatting from the menu
because its only got three conditions I could be using many more.

Also I don't know how code can run automatically when a person changes the
value of a cell in a sheet.

Could anyone help

Thanks

Suzanne

Gord,
I have a similar situation, except that I have a column of cells (the range
can vary) where a user can input a code (0-20), and based on the code
inputted, I need to change both the font color and cell color.
Given that I am a VBA novice, can you kindly tell me how to modify your code
to accommodate my particular situation?
Thanks,
Bob


"Gord Dibben" wrote:

Adjust the "A" and "B" etc. to suit.

Option Compare Text
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("B2"))
'Set vRngInput = Intersect(Target, Range("B2:B10")) for larger range of cells
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case 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

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the above into that module.


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 13:28:01 -0800, Grd wrote:

Hi everyone,

I need to change the color of a cell based on the value the user types in
the cell.
In this cell B2. If they type ON then it should be blue, BC red, AL yellow,
NS green and PEI pink. I can't use conditional formatting from the menu
because its only got three conditions I could be using many more.

Also I don't know how code can run automatically when a person changes the
value of a cell in a sheet.

Could anyone help

Thanks

Suzanne

Bob,

The colorindex numbers below are made up - you will need to modify them, as well as add another 15
cases...

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num1 As Integer
Dim Num2 As Integer
Dim rng As Range
Dim vRngInput As Variant

'Optional - if you want to limit to one cell at a time
' If Target.Cells.Count 1 Then Exit Sub

Set vRngInput = Intersect(Target, Range("B2:B1000"))
If vRngInput Is Nothing Then Exit Sub

On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = 1:
Num1 = 10
Num2 = 12
Case Is = 2:
Num1 = 3
Num2 = 12
Case Is = 3:
Num1 = 4
Num2 = 3
Case Is = 4:
Num1 = 6
Num2 = 7
Case Is = 5:
Num1 = 8
Num2 = 9
End Select
'Apply the color
rng.Interior.ColorIndex = Num1
rng.Font.ColorIndex = Num2

Next rng
endit:
Application.EnableEvents = True
End Sub


"Bob" wrote in message
...
Gord,
I have a similar situation, except that I have a column of cells (the range
can vary) where a user can input a code (0-20), and based on the code
inputted, I need to change both the font color and cell color.
Given that I am a VBA novice, can you kindly tell me how to modify your code
to accommodate my particular situation?
Thanks,
Bob


"Gord Dibben" wrote:

Adjust the "A" and "B" etc. to suit.

Option Compare Text
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("B2"))
'Set vRngInput = Intersect(Target, Range("B2:B10")) for larger range of cells
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case 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

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the above into that module.


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 13:28:01 -0800, Grd wrote:

Hi everyone,

I need to change the color of a cell based on the value the user types in
the cell.
In this cell B2. If they type ON then it should be blue, BC red, AL yellow,
NS green and PEI pink. I can't use conditional formatting from the menu
because its only got three conditions I could be using many more.

Also I don't know how code can run automatically when a person changes the
value of a cell in a sheet.

Could anyone help

Thanks

Suzanne