Con ditional Formating-Need 4 condidtions

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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.

 

[JE McGimpsey]

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

 

[Bob Phillips]

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