It seems to me that I need an event Macro since I cannot get what I want
acomplished using the Conventional Formatting option. I have tried so many
times writing this macro that I am now confused.
I need a way to color a cell based on an imput to the same cell.
example:
Type GY9 in cell A1 and the bacground color automaticaly would change to
[color12]
I think If someone can give me the formula to this example above, I think I
can add the other 12 colors needed 1 for each set of cell imputs.
thanks

Hi Nick,
http://www.mvps.org/dmcritchie/excel/event.htm#case
to install right click on the worksheet tab, view code.

If you want to use Extended Conditional Formatting in the same
manner as conditional formatting, you could use
http://www.xldynamic.com/source/xld.html

Normal Conditional Formatting (limited to 3 colors)
http://www.mvps.org/dmcritchie/excel/condfmt.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"nick s" wrote in message ...
It seems to me that I need an event Macro since I cannot get what I want
acomplished using the Conventional Formatting option. I have tried so many
times writing this macro that I am now confused.
I need a way to color a cell based on an imput to the same cell.
example:
Type GY9 in cell A1 and the bacground color automaticaly would change to
[color12]
I think If someone can give me the formula to this example above, I think I
can add the other 12 colors needed 1 for each set of cell imputs.
thanks

Hi David, I tried your code earlier and I couldn't get it to work.
Here is my attempt....

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2000-08-08 rev. 2000-08-14
' http://www.mvps.org/dmcritchie/excel/event.htm
Dim vLetter As String
Dim vColor As Integer
Dim cRange As Range
Dim cell As Range
'***************** check range ****
Set cRange = Intersect(Range("E2:E99"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
'**********************************

For Each cell In Target
vLetter = UCase(Left(cell.Value & " ", 1))
'see colors.htm and event.htm in same directory as
' http://www.mvps.org/dmcritchie/excel/excel.htm
vColor = 0 'default is no color
Select Case vLetter
Case "GF7"
vColor = 34
Case "GY9"
vColor = 36
Case "EV2"
vColor = 39
Case "EL5"
vColor = 41
Case "FJ6"
vColor = 38
Case "GY8"
vColor = 37
Case "FY1"
vColor = 35
Case "GA4"
vColor = 34
Case "FE5"
vColor = 36
Case "GB5"
vColor = 39
Case "GK6"
vColor = 41
Case "GB7"
vColor = 38
Case "GY4"
vColor = 37
Case "GE7"
vColor = 35
Case "GF3"
vColor = 39
Case "GT2"
vColor = 41
Case "GT8"
vColor = 38
Case "EW1"
vColor = 37
Case "TX9"
vColor = 35
End Select
Application.EnableEvents = False 'should be part of Change macro
cell.Interior.ColorIndex = vColor
Application.EnableEvents = True 'should be part of Change macro
Next cell
'Target.Offset(0, 1).Interior.colorindex = vColor
' use Text instead of Interior if you prefer
End Sub



"David McRitchie" wrote:

Hi Nick,
http://www.mvps.org/dmcritchie/excel/event.htm#case
to install right click on the worksheet tab, view code.

If you want to use Extended Conditional Formatting in the same
manner as conditional formatting, you could use
http://www.xldynamic.com/source/xld.html

Normal Conditional Formatting (limited to 3 colors)
http://www.mvps.org/dmcritchie/excel/condfmt.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"nick s" wrote in message ...
It seems to me that I need an event Macro since I cannot get what I want
acomplished using the Conventional Formatting option. I have tried so many
times writing this macro that I am now confused.
I need a way to color a cell based on an imput to the same cell.
example:
Type GY9 in cell A1 and the bacground color automaticaly would change to
[color12]
I think If someone can give me the formula to this example above, I think I
can add the other 12 colors needed 1 for each set of cell imputs.
thanks

Hi Nick,
This is limiting the test to 1 character, but you are checking 3 characters.
vLetter = UCase(Left(cell.Value & " ", 1))

What can I do for 3 characters?

"David McRitchie" wrote:

Hi Nick,
This is limiting the test to 1 character, but you are checking 3 characters.
vLetter = UCase(Left(cell.Value & " ", 1))

Change the 1 to a 3 or just use the UCase(cell.value) depending
on what you nee.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"nick s" wrote in message ...
What can I do for 3 characters?

"David McRitchie" wrote:

Hi Nick,
This is limiting the test to 1 character, but you are checking 3 characters.
vLetter = UCase(Left(cell.Value & " ", 1))

thank you, that worked......

"David McRitchie" wrote:


Change the 1 to a 3 or just use the UCase(cell.value) depending
on what you nee.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"nick s" wrote in message ...
What can I do for 3 characters?

"David McRitchie" wrote:

Hi Nick,
This is limiting the test to 1 character, but you are checking 3 characters.
vLetter = UCase(Left(cell.Value & " ", 1))

David, is there a way to get the font to change colors, as in white font with
dark color of cell and visa versa, black font with lighter color cells?

thanks again, Nick

"David McRitchie" wrote:


Change the 1 to a 3 or just use the UCase(cell.value) depending
on what you nee.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"nick s" wrote in message ...
What can I do for 3 characters?

"David McRitchie" wrote:

Hi Nick,
This is limiting the test to 1 character, but you are checking 3 characters.
vLetter = UCase(Left(cell.Value & " ", 1))

see my colors.htm page
http://www.mvps.org/dmcritchie/excel/colors.htm
be aware that providing an interior color will wipe out gridlines, so you would
have to use borders.
http://www.mvps.org/dmcritchie/excel/gridline.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"nick s" wrote in message ...
David, is there a way to get the font to change colors, as in white font with
dark color of cell and visa versa, black font with lighter color cells?

thanks again, Nick

"David McRitchie" wrote:


Change the 1 to a 3 or just use the UCase(cell.value) depending
on what you nee.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"nick s" wrote in message ...
What can I do for 3 characters?

"David McRitchie" wrote:

Hi Nick,
This is limiting the test to 1 character, but you are checking 3 characters.
vLetter = UCase(Left(cell.Value & " ", 1))