Formatting

Jelinek · 04-27-2006, 05:50 AM

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

Jim Rech · 04-27-2006, 07:10 AM

Jelinek · 04-27-2006, 08:00 AM

**Bryan Hessey** · 04-27-2006, 08:50 AM

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

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

note, Selection.Text would need to be set.

Hope this helps

--

Originally Posted by Jelinek

Jelinek · 04-27-2006, 09:25 AM

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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > |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
>
>

Jim Rech · 04-27-2006, 09:35 AM

My thought re using the sheet Calculate event is basically what Byran posted
except for the range he uses. His macro affects the Selection, so only the
cells in it would get processed when a calc occurs. You'd want to use
something else I think. It could be the sheet's UsedRange but if it is
large that could take quite a while. If you had named the range that
contains all the cells that you want processed that would be better. Say
all the cells that could have one of your triggering values was in a range
named "TriggerRg". Then the calc handler would look like this:

Option Compare Text ''Disregard case
Private Sub Worksheet_Calculate()
Dim Cell As Range
For Each Cell In Range("TriggerRg").SpecialCells(xlCellTypeConstants,
xlTextValues)
Select Case Cell.Value
Case "Failed"
Cell.Interior.ColorIndex = 6
Case "File Failure"
Cell.Interior.ColorIndex = 7
Case "Active"
Cell.Interior.ColorIndex = 3
Case "Successful"
Cell.Interior.ColorIndex = 4
Case "Queued"
Cell.Interior.ColorIndex = 5
End Select
Next
End Sub

I'm sure you'll need to fine-tune this but it's a start.

--
Jim
"Jelinek" <[email protected]> wrote in message
news:[email protected]...
| 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" <[email protected]> wrote in message
| > news:[email protected]...
| > |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
| >
| >
| >

**jim brown** · 04-27-2006, 10:11 AM

hi
i am attempting to build a RAG report on service levels achieved,but with five colours all dependant on the cell value being between two percentages ie >5% and <7%. I am new to vba so could someone show me the first few lines and i will work from there.

thanks
jim