Your only option currently (Excel 2007 will have virtually unlimited
conditions) I think is to run a variation of this macro manually or when
some other event occurs. For example, you could run it via the worksheet
Calculate event. You'd have to modify the code to examine a preset range
rather than the passed Target range however.

--
Jim
"Jelinek" wrote in message
...
|I posed the following questions
|
| The problem is
| I have five conditions
|
| Failed
| File Failure
| Active
| Successful
| Queued
|
| I would like to assign a colour code to the cells when they equal one of
the
| five conditions the way conditional formatting is set up i only allows
three
| contditions?
|
| is there anything that can be done to add another contdition
|
| and was given the following response
|
| Create a worksheet "SelectionChange" macro.
|
| Private Sub Worksheet_SelectionChange(ByVal Target As Range)
| If Selection.Text = "Failed" Then
| With Selection.Interior
| .ColorIndex = 6
| .Pattern = xlSolid
| .PatternColorIndex = xlAutomatic
| End With
| End If
| If Selection.Text = "File Failure" Then
| With Selection.Interior
| .ColorIndex = 7
| .Pattern = xlSolid
| .PatternColorIndex = xlAutomatic
| End With
| End If
| If Selection.Text = "Active" Then
| With Selection.Interior
| .ColorIndex = 3
| .Pattern = xlSolid
| .PatternColorIndex = xlAutomatic
| End With
| End If
| If Selection.Text = "Successful" Then
| With Selection.Interior
| .ColorIndex = 4
| .Pattern = xlSolid
| .PatternColorIndex = xlAutomatic
| End With
| End If
| If Selection.Text = "Queued" Then
| With Selection.Interior
| .ColorIndex = 5
| .Pattern = xlSolid
| .PatternColorIndex = xlAutomatic
| End With
| End If
| End Sub
|
| The problem is that for the formatting to occur a manual change is
required
| these cells are affected by VLookup and when the contents change the
| formatting above does not get applied until the cell is activated using
the
| cursor has anyone got alternative solution?
|
| Many Thanks