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?

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?

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?

One way:

If the values are hand entered:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim vColors As Variant
Dim nLBound As Long
Dim i As Long
With Target
If .Count 1 Then Exit Sub
If Not Intersect(.Cells, Range("G1:G400")) Is Nothing Then
vColors = Array(Array("Red", 3), _
Array("Yellow", 6), _
Array("Blue", 5), _
Array("Green", 10))
nLBound = LBound(vColors)
For i = nLBound To UBound(vColors)
If .Text = vColors(i)(nLBound) Then _
.Font.ColorIndex = vColors(i)(nLBound + 1)
Next i
End If
End With
End Sub

If the values are calculated:

Private Sub Worksheet_Calculate()
Dim vColors As Variant
Dim rCell As Range
Dim nLBound As Long
Dim i As Long
Dim sTemp As String

vColors = Array(Array("Red", 3), _
Array("Yellow", 6), _
Array("Blue", 5), _
Array("Green", 10))
nLBound = LBound(vColors)
For Each rCell In Range("F1:F400")
With rCell
sTemp = .Text
For i = nLBound To UBound(vColors)
If sTemp = vColors(i)(nLBound) Then _
.Font.ColorIndex = vColors(i)(nLBound + 1)
Next i
End With
Next rCell
End Sub


In article ,
CB wrote:

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?

'-----------------------------------------------------------------
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?