Another VB Code Question

[Stan]

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!

 

[JLGWhiz]

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

 

[JLGWhiz]

Had an unnecessary line in there:
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]

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

 

[OssieMac]

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

 

[JLGWhiz]

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

 

[JLGWhiz]

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