Cell Shading > 3 conditions

[rabsparks]

I have a spreadsheet that contains up to 6 different text strings. I'd
like to automatically change the text color or the cell shading
depending on the specific condition. For example, if the cell contains
"A", make the cell shading red. If it contains "B", change the font
color to red. Excel's conditional formatting limits me to 3 conditions.
How can I accomplish this objective?

I'm using Office 98 on a Mac, and worse, I'm not VBA smart.

Thanks.
rick

 

[Frank Kabel]

Hi
try the following addin:
http://www.xldynamic.com/source/xld.CFPlus.Download.html

 

[JE McGimpsey]

One way:

CTRL-click the worksheet tab to enter the Visual Basic Editor (VBE).
Paste one of these into the module that opens...

If the values are entered by the user:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("A1:A5,B2:C3")) Is Nothing Then
.Interior.ColorIndex = xlColorIndexNone
.Font.ColorIndex = xlColorIndexAutomatic
Select Case .Value
Case "A"
.Interior.ColorIndex = 3
Case "B"
.Font.ColorIndex = 3
Case "C"
.Interior.ColorIndex = 5
Case "D"
.Font.ColorIndex = 5
Case "E"
.Interior.ColorIndex = 6
Case "F"
.Font.ColorIndex = 6
End Select
End If
End With
End Sub

If the values are calculated:

Private Sub Worksheet_Calculate()
Dim rCell As Range
For Each rCell In Range("A1:A5,B2:C3")
With rCell
.Interior.ColorIndex = xlColorIndexNone
.Font.ColorIndex = xlColorIndexAutomatic
Select Case rCell.Value
Case "A"
.Interior.ColorIndex = 3
Case "B"
.Font.ColorIndex = 3
Case "C"
.Interior.ColorIndex = 5
Case "D"
.Font.ColorIndex = 5
Case "E"
.Interior.ColorIndex = 6
Case "F"
.Font.ColorIndex = 6
End Select
End With
Next rCell
End Sub

Change the .Interior or .Font to suit. Change the range reference to
suit.

While in the VBE, search for the ColorIndex property in Help. There's a
palette there that will tell you what numbers to use for which colors.

 

[JE McGimpsey]

As a Mac user, he may not be able to easily view that page. The
javascript on the xldynamic site breaks in the Safari browser. Mozilla
(Firefox, Camino) is more tolerant. Of course, turning off javascript
works, too.

 

[Frank Kabel]

Hi
forget this solution and use JE's code example. I just recognised that
you're using a MAC and unfortunately the addin will not run on this
platform!