MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional formating for more than 3 conditions


Posted by Ryan W on July 19, 2000 12:24 PM

I was wondering if anyone has created a script to be able to conditionally format in Excel 2000. I have used the internal functionality but I need it to have 5 conditions and the CF function is limited to 3 conditions.
I have tried a couple of different things but I can't get around the restriction of only 3 conditions.

Please let help if you have any ideas.

Ryan


Posted by dBoy on July 20, 0100 1:53 AM

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

Posted by Ivan Moala on July 22, 0100 4:52 AM

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

Posted by Ryan W on July 20, 0100 6:18 AM

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

Posted by Ryan on July 19, 0100 12:29 PM

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

Posted by Ryan W on July 19, 0100 12:52 PM

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.

Posted by Ryan W on July 24, 0100 1:02 PM

Ivan,

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

Ryan W