'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "Red": .Interior.ColorIndex = 3 'red
Case "Yellow" .Interior.ColorIndex = 6 'yellow
Case "Blue": .Interior.ColorIndex = 5 'blue
Case "Green": .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"CB" wrote in message
...
Can I use VBA to color the cells rather than using conditional formatting?
Can someone tell me how to use VBA to look at Column F Row 1 thru 400 and
if
the text Red is in the cell color the cell red? Will need to duplicate
for
yellow, green and blue.

"Tom Ogilvy" wrote:

If it must be one of the 4 colors, then color the cell blue manually as
the
default.

the conditional format will account for the other 3.

Yes, 3 is the limit plus the default color.

--
Regards,
Tom Ogilvy


"CB" wrote:

I posted this first in the general questions area and did not receive a
response.

I need to add a fourth condition to a column. I have already used 3
which is
apparently the limit that excel will allow. I have a column that
contains
the text Red, Green, Yellow or Blue. I have set the conditional
formatting
for Red, Green and yellow and everything works fine. I need to add an
additional condition to turn the cell Blue if it contains the text
"Blue".

Is VBA the answer for this? If so can someone help me?