Formatting

[Guest]

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]

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
|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

 

[Guest]

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?

 

[Bryan Hessey]

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

--

 

[Guest]

Bryan

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

Thanks

 

[Jim Rech]

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
| 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
| > | > |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]

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