Conditional formating for more than 3 conditions

If you think something might be wrong with your code try posting that

Ryan Try this;

In a worksheet module (the one with the CF range)
Place this code in.
Change MyRg to your range to conditionally Fmt.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MyRg As Range 'Range to Format
Dim CFormat

Set MyRg = Range("A1:A10")
Set CFormat = Application.Intersect(Target, MyRg)

If Not CFormat Is Nothing Then
Call FormatCell(Target.Value, Target.Address)
End If

End Sub

In a Module place this code;
Which is your code Modified to work.
NB: try diff color codes.
In your orig code you had 75
Range = 1 - 56 Std. Just experiment.

Sub FormatCell(No, CellAdr)

Select Case No
Case Is = 1
With Range(CellAdr).Interior
.ColorIndex = 45
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Case Is = 2
With Range(CellAdr).Interior
.ColorIndex = 23
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Case Is = 3
With Range(CellAdr).Interior
.ColorIndex = 25
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Case Is = 4
With Range(CellAdr).Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Case Is = 5
With Range(CellAdr).Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Case Else
Range(CellAdr).Interior.ColorIndex = xlNone

End Select
End Sub

HTH

Ivan

I am not sure if this would ever work even with the write syntax but here is what I tried.

Sub Macro1()

Select Case ActiveCell.Value
Case Is = 1
With Selection.Interior
.ColorIndex = 45
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Select Case ActiveCell.Value
Case Is = 2
With Selection.Interior
.ColorIndex = 23
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Select Case ActiveCell.Value
Case Is = 3
With Selection.Interior
.ColorIndex = 75
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Select Case ActiveCell.Value
Case Is = 4
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Select Case ActiveCell.Value
Case Is = 5
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

End Select
End Sub

Ryan,

Somebody else was asking about this a little while ago, but he never followed up on it so if you want to give some more information on what you need done, somebody can take a look at it.

Ryan

I am setting up a spread sheet to show time utilization. It will be updated by different people so I want to have a color code to be able to distiguish which people are available, who are not, etc.
The user types either 0, 1, 2, or 3 (for now) and then the cell is automatically changed to green, red, blue, or left the original default color depending on the number entered.
The problem is I need two other catagories and I can't seem to get a VB script to work.

Ivan,

Thank you for helping me out. It works perfectly, I really appreciate it.

Ryan W