Condition Formating > 3 Condintions: Can It be done?

[Banner01]

Kind of a begginner with Macros. Hope someone can help. I am using Conditional Formating to highlight a group of cells with a given value. These values autoupdate from data data on other worksheets. I have 10 values I am trying to color code but conditional formating only allows 3 conditions. I tried to manually do this with VB script (Stare and Compare...a begginers best friend) but the macro errors out after the 3rd condition. Any ideas:

Sub Color()
'
' Color Macro
' Macro recorded 3/27/02 by
'

'
Range("A1:P34").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$B$39"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$B$40"
Selection.FormatConditions(2).Interior.ColorIndex = 38
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$B$41"
Selection.FormatConditions(3).Interior.ColorIndex = 8
End Sub

 

[Von Pookie]

Yes.

Try here for starters...

Also, using the search feature before posting can sometimes save some time...

_________________
Kristy

"There are two means of refuge from the miseries of life: music and cats." - Albert Schweitzer
This message was edited by Von Pookie on 2002-03-27 13:57

 

[Banner01]

Did a search, guesse it wasn't as good as yours. Tried to apply the solutions given but to no avail. I did find this with another source. It applied directly to what I was trying to do. Hope this helps someone with the same problem.Sub Add_Color()

'AII Red 3
'IITX Light Purple 38
'COCOMP Pale Blue 8
'JTR Light Green 35
'MCOMP Yellow 36
'TRCOMP Yellow 36
'TMCOMP Purple 39
'TECOMP Purple 39
'NO WORK Purple 39
'IGNORE Purple 39

Dim TmpString As String
Dim TmpColor As Integer

For X = 1 To 30

Range(Cells(3, X), Cells(3, X)).Select
If ActiveCell.Value = "" Then GoTo X_DONE

For Y = 3 To 10000
Range(Cells(Y, X), Cells(Y, X)).Select
If ActiveCell.Value = "" Then GoTo Y_DONE

TmpString = ActiveCell.Value
TmpString = Mid(TmpString, 1, 3)

TmpColor = 0
If TmpString = "AII" Then TmpColor = 3
If TmpString = "IIT" Then TmpColor = 38
If TmpString = "COC" Then TmpColor = 8
If TmpString = "JTR" Then TmpColor = 35
If TmpString = "MCO" Then TmpColor = 36
If TmpString = "TRC" Then TmpColor = 36
If TmpString = "TMC" Then TmpColor = 39
If TmpString = "TEC" Then TmpColor = 39
If TmpString = "NO " Then TmpColor = 39
If TmpString = "IGN" Then TmpColor = 39

If TmpColor > 0 Then
With Selection.Interior
.ColorIndex = TmpColor
.Pattern = xlSolid
End With
End If

Next Y
Y_DONE:

Next X

X_DONE:

End Sub