Can someone help me with the code necessary for the following:

When a cell says the word 'Green' then the cell should be color coded Green.
When the cell says 'Red' then the cell should be color coded Red and so on
for the following colors:

Red
Green
Yellow
Blue
Orange


Many thanks in advance!

Put this in the worksheet code module. You can add additional colors, but be
sure to use all lower case in the code for their names. The code is written
so that no matter how the user puts it into the sheet, it will be read as
lower case.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
If Target < "" Then
Select Case LCase(Target.Value)
Case "red"
Target.Interior.ColorIndex = 3
Case "green"
Target.Interior.ColorIndex = 10
Case "yellow"
Target.Interior.ColorIndex = 6
End Select
End If
End Sub



"Stan" wrote:

Can someone help me with the code necessary for the following:

When a cell says the word 'Green' then the cell should be color coded Green.
When the cell says 'Red' then the cell should be color coded Red and so on
for the following colors:

Red
Green
Yellow
Blue
Orange


Many thanks in advance!

Had an unnecessary line in the
Private Sub Worksheet_Change(ByVal Target As Range)
If Target < "" Then
Select Case LCase(Target.Value)
Case "red"
Target.Interior.ColorIndex = 3
Case "green"
Target.Interior.ColorIndex = 10
Case "yellow"
Target.Interior.ColorIndex = 6
End Select
End If
End Sub





"Stan" wrote:

Can someone help me with the code necessary for the following:

When a cell says the word 'Green' then the cell should be color coded Green.
When the cell says 'Red' then the cell should be color coded Red and so on
for the following colors:

Red
Green
Yellow
Blue
Orange


Many thanks in advance!

Can you tell me what the ColorIndex is for Orange and Blue?

"JLGWhiz" wrote:

Had an unnecessary line in the
Private Sub Worksheet_Change(ByVal Target As Range)
If Target < "" Then
Select Case LCase(Target.Value)
Case "red"
Target.Interior.ColorIndex = 3
Case "green"
Target.Interior.ColorIndex = 10
Case "yellow"
Target.Interior.ColorIndex = 6
End Select
End If
End Sub





"Stan" wrote:

Can someone help me with the code necessary for the following:

When a cell says the word 'Green' then the cell should be color coded Green.
When the cell says 'Red' then the cell should be color coded Red and so on
for the following colors:

Red
Green
Yellow
Blue
Orange


Many thanks in advance!

Hi Stan,

If you use color in lieu of colorindex then you are not limited to the 56
color index constants that do not include orange but you can use some of the
standard color vbconstants where they are available in lieu of their value.

If you want any other color just record a sample of code by setting the
color and you will get the numeric value for it. (Recording gives additional
code but just extract the required color code and use in the following
example.)

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case LCase(Target.Value)
Case "red"
Target.Interior.Color = vbRed
Case "green"
Target.Interior.Color = vbGreen
Case "yellow"
Target.Interior.Color = vbYellow
Case "blue"
Target.Interior.Color = vbBlue
Case "orange"
Target.Interior.Color = 49407
Case Else
Target.Interior.Color = xlColorIndexNone

End Select

End Sub


--
Regards,

OssieMac


"Stan" wrote:

Can you tell me what the ColorIndex is for Orange and Blue?

"JLGWhiz" wrote:

Had an unnecessary line in the
Private Sub Worksheet_Change(ByVal Target As Range)
If Target < "" Then
Select Case LCase(Target.Value)
Case "red"
Target.Interior.ColorIndex = 3
Case "green"
Target.Interior.ColorIndex = 10
Case "yellow"
Target.Interior.ColorIndex = 6
End Select
End If
End Sub





"Stan" wrote:

Can someone help me with the code necessary for the following:

When a cell says the word 'Green' then the cell should be color coded Green.
When the cell says 'Red' then the cell should be color coded Red and so on
for the following colors:

Red
Green
Yellow
Blue
Orange


Many thanks in advance!

Hey, I found it. Orange is ColorIndex 45.

The site below gives you all the color info you ever wanted to know and were
afraid to ask.

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

"OssieMac" wrote:

Hi Stan,

If you use color in lieu of colorindex then you are not limited to the 56
color index constants that do not include orange but you can use some of the
standard color vbconstants where they are available in lieu of their value.

If you want any other color just record a sample of code by setting the
color and you will get the numeric value for it. (Recording gives additional
code but just extract the required color code and use in the following
example.)

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case LCase(Target.Value)
Case "red"
Target.Interior.Color = vbRed
Case "green"
Target.Interior.Color = vbGreen
Case "yellow"
Target.Interior.Color = vbYellow
Case "blue"
Target.Interior.Color = vbBlue
Case "orange"
Target.Interior.Color = 49407
Case Else
Target.Interior.Color = xlColorIndexNone

End Select

End Sub


--
Regards,

OssieMac


"Stan" wrote:

Can you tell me what the ColorIndex is for Orange and Blue?

"JLGWhiz" wrote:

Had an unnecessary line in the
Private Sub Worksheet_Change(ByVal Target As Range)
If Target < "" Then
Select Case LCase(Target.Value)
Case "red"
Target.Interior.ColorIndex = 3
Case "green"
Target.Interior.ColorIndex = 10
Case "yellow"
Target.Interior.ColorIndex = 6
End Select
End If
End Sub





"Stan" wrote:

Can someone help me with the code necessary for the following:

When a cell says the word 'Green' then the cell should be color coded Green.
When the cell says 'Red' then the cell should be color coded Red and so on
for the following colors:

Red
Green
Yellow
Blue
Orange


Many thanks in advance!

You are so right. XL2002 help does not display the correct colors for some of
the indexes and I have never looked closely or tested them before. I just
thought that orange was not one of them. (44 and 45 are a dull green color
like index 12).

I cannot even find the matrix in xl2007 Help.

I ran the following little test and compared them to the xl2002 matix in
Help. There are a few colors incorrectly displayed in Help.

Sub testColorIndex()
Dim i As Integer

For i = 1 To 56
Cells(i, "A").Interior.ColorIndex = i
Next i

End Sub

Also thanks for the link to color info. Looks good.

--
Regards,

OssieMac


"JLGWhiz" wrote:

Hey, I found it. Orange is ColorIndex 45.

The site below gives you all the color info you ever wanted to know and were
afraid to ask.

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

"OssieMac" wrote:

Hi Stan,

If you use color in lieu of colorindex then you are not limited to the 56
color index constants that do not include orange but you can use some of the
standard color vbconstants where they are available in lieu of their value.

If you want any other color just record a sample of code by setting the
color and you will get the numeric value for it. (Recording gives additional
code but just extract the required color code and use in the following
example.)

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case LCase(Target.Value)
Case "red"
Target.Interior.Color = vbRed
Case "green"
Target.Interior.Color = vbGreen
Case "yellow"
Target.Interior.Color = vbYellow
Case "blue"
Target.Interior.Color = vbBlue
Case "orange"
Target.Interior.Color = 49407
Case Else
Target.Interior.Color = xlColorIndexNone

End Select

End Sub


--
Regards,

OssieMac


"Stan" wrote:

Can you tell me what the ColorIndex is for Orange and Blue?

"JLGWhiz" wrote:

Had an unnecessary line in the
Private Sub Worksheet_Change(ByVal Target As Range)
If Target < "" Then
Select Case LCase(Target.Value)
Case "red"
Target.Interior.ColorIndex = 3
Case "green"
Target.Interior.ColorIndex = 10
Case "yellow"
Target.Interior.ColorIndex = 6
End Select
End If
End Sub





"Stan" wrote:

Can someone help me with the code necessary for the following:

When a cell says the word 'Green' then the cell should be color coded Green.
When the cell says 'Red' then the cell should be color coded Red and so on
for the following colors:

Red
Green
Yellow
Blue
Orange


Many thanks in advance!

Blue is ColorIndex 5, I am not sure that Orange has one. You can open the
VBE with Alt + F11 then in the Help search box type "PatternColorIndex
property and then click on that when it comes up in the help options window.
It will display a color palette with the numbers. The colors displyed in the
chart may vary in actual application for some of the ones that appear as gray
on the chart, but you can play with it and get the colors you want with the
corresponding index number.

"Stan" wrote:

Can you tell me what the ColorIndex is for Orange and Blue?

"JLGWhiz" wrote:

Had an unnecessary line in the
Private Sub Worksheet_Change(ByVal Target As Range)
If Target < "" Then
Select Case LCase(Target.Value)
Case "red"
Target.Interior.ColorIndex = 3
Case "green"
Target.Interior.ColorIndex = 10
Case "yellow"
Target.Interior.ColorIndex = 6
End Select
End If
End Sub





"Stan" wrote:

Can someone help me with the code necessary for the following:

When a cell says the word 'Green' then the cell should be color coded Green.
When the cell says 'Red' then the cell should be color coded Red and so on
for the following colors:

Red
Green
Yellow
Blue
Orange


Many thanks in advance!