Bryan

Copied the code as suggested however it does not seem to have affected the
sheet in question.

Thanks

"Bryan Hessey" wrote:


Would it not be required to activate on Worksheet (re-) Calculate?
something like:


Code:
--------------------

Private Sub Worksheet_Calculate()

Dim iColour as integer

If Selection.Text = "Failed" Then
iColour = 6
Else
If Selection.Text = "File Failure" Then
iColour = 7
Else
If Selection.Text = "Active" Then
iColour = 3
Else
If Selection.Text = "Successful" Then
iColour = 4
Else
If Selection.Text = "Queued" Then
iColour = 5
End If
End If
End If
End If
End If

With Selection.Interior
..ColorIndex = iColour
..Pattern = xlSolid
..PatternColorIndex = xlAutomatic
End With
--------------------


Hope this helps

--

Jelinek Wrote:
Jim

Thanks for this, as my VBA is basic have yougot any ideas on what i
would
have to modify so I could look at these two other options as
suggested?


"Jim Rech" wrote:

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





--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=536738