It seems to me that I need an event Macro,

[Guest]

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

 

[David McRitchie]

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

 

[Guest]

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

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

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

 

[David McRitchie]

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

 

[Guest]

What can I do for 3 characters?

 

[David McRitchie]

Change the 1 to a 3 or just use the UCase(cell.value) depending
on what you nee.

 

[Guest]

thank you, that worked......

Change the 1 to a 3 or just use the UCase(cell.value) depending
on what you nee.

 

[Guest]

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]

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