Conditional Formatting (Excel 2002 SP3)
 

Hi, I have values in a cell that changes if the cell next to it changes.
The values are five constant values, i.e. "Critical Criteria", "Non-Critical
Criteria" etc.
I need to colour the cell a different colour based on each unique value.

The conditional formatting only caters for 3 conditions. How do I do this. I
do not have knowledge of using macros.

Appreciate it.

Conditional Formatting (Excel 2002 SP3)
 

Try event code. An example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"vertigo" wrote in message
...
Hi, I have values in a cell that changes if the cell next to it changes.
The values are five constant values, i.e. "Critical Criteria",
"Non-Critical
Criteria" etc.
I need to colour the cell a different colour based on each unique value.

The conditional formatting only caters for 3 conditions. How do I do this.
I
do not have knowledge of using macros.

Appreciate it.

Conditional Formatting (Excel 2002 SP3)
 

I have done it - what do I need to do next?

"Bob Phillips" wrote:

Try event code. An example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"vertigo" wrote in message
...
Hi, I have values in a cell that changes if the cell next to it changes.
The values are five constant values, i.e. "Critical Criteria",
"Non-Critical
Criteria" etc.
I need to colour the cell a different colour based on each unique value.

The conditional formatting only caters for 3 conditions. How do I do this.
I
do not have knowledge of using macros.

Appreciate it.

Conditional Formatting (Excel 2002 SP3)
 

XL2007 you can get as many colors as you want


"vertigo" wrote:

Hi, I have values in a cell that changes if the cell next to it changes.
The values are five constant values, i.e. "Critical Criteria", "Non-Critical
Criteria" etc.
I need to colour the cell a different colour based on each unique value.

The conditional formatting only caters for 3 conditions. How do I do this. I
do not have knowledge of using macros.

Appreciate it.

Conditional Formatting (Excel 2002 SP3)
 

You need to change it for your range of cells, and change the case
statements for your values. Then change a cell and it should change.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"vertigo" wrote in message
...
I have done it - what do I need to do next?

"Bob Phillips" wrote:

Try event code. An example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"vertigo" wrote in message
...
Hi, I have values in a cell that changes if the cell next to it
changes.
The values are five constant values, i.e. "Critical Criteria",
"Non-Critical
Criteria" etc.
I need to colour the cell a different colour based on each unique
value.

The conditional formatting only caters for 3 conditions. How do I do
this.
I
do not have knowledge of using macros.

Appreciate it.

Conditional Formatting (Excel 2002 SP3)
 

Sorry, but this only works if I actually select the cell, change the value
and move to another cell, and it only works for the cell that's been changed.

Is there any other event that I can use: the cells are automatically updated
via calculations - so I don't physically go and change them? Also, how do I
change the colour of the cell in the same row next column?

Sorry for the trouble

"vertigo" wrote:

I have done it - what do I need to do next?

"Bob Phillips" wrote:

Try event code. An example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"vertigo" wrote in message
...
Hi, I have values in a cell that changes if the cell next to it changes.
The values are five constant values, i.e. "Critical Criteria",
"Non-Critical
Criteria" etc.
I need to colour the cell a different colour based on each unique value.

The conditional formatting only caters for 3 conditions. How do I do this.
I
do not have knowledge of using macros.

Appreciate it.