Conditional formatting
 

how do I get more than 3 conditional formats?
I want to shade the cells depending on the 9 values (criteria range) each
one a different colour

thanks
--
CHRISK

you will need to write a macro to do 9 colors.

"CHRIS K" wrote:

how do I get more than 3 conditional formats?
I want to shade the cells depending on the 9 values (criteria range) each
one a different colour

thanks
--
CHRISK

An example

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

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

RP
(remove nothere from the email address if mailing direct)


"CHRIS K" wrote in message
...
how do I get more than 3 conditional formats?
I want to shade the cells depending on the 9 values (criteria range) each
one a different colour

thanks
--
CHRISK

How?
have nt done one before!
--
CHRISK


"bj" wrote:

you will need to write a macro to do 9 colors.

"CHRIS K" wrote:

how do I get more than 3 conditional formats?
I want to shade the cells depending on the 9 values (criteria range) each
one a different colour

thanks
--
CHRISK

See my response earlier.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"CHRIS K" wrote in message
...
How?
have nt done one before!
--
CHRISK


"bj" wrote:

you will need to write a macro to do 9 colors.

"CHRIS K" wrote:

how do I get more than 3 conditional formats?
I want to shade the cells depending on the 9 values (criteria range)
each
one a different colour

thanks
--
CHRISK

bit scared to mess with the code incase I goof it
will try tomorrow
have to go out on site now
thanks
--
CHRISK


"Bob Phillips" wrote:

An example

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

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

RP
(remove nothere from the email address if mailing direct)


"CHRIS K" wrote in message
...
how do I get more than 3 conditional formats?
I want to shade the cells depending on the 9 values (criteria range) each
one a different colour

thanks
--
CHRISK

for shading those colors may be a bit dark, you might prefer
pastel colors. You will find a little table of the colorindex values
on my page http://www.mvps.org/dmcritchie/excel/event.htm#case

or if you look in your VBA Help for colorindex (not your Excel help).

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Bob Phillips" wrote in message ...
An example

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

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

RP
(remove nothere from the email address if mailing direct)


"CHRIS K" wrote in message
...
how do I get more than 3 conditional formats?
I want to shade the cells depending on the 9 values (criteria range) each
one a different colour

thanks
--
CHRISK

How does it know the value
and how does that relate to the select case
--
CHRISK


"Bob Phillips" wrote:

An example

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

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

RP
(remove nothere from the email address if mailing direct)


"CHRIS K" wrote in message
...
how do I get more than 3 conditional formats?
I want to shade the cells depending on the 9 values (criteria range) each
one a different colour

thanks
--
CHRISK

It knows the value because the changed value is passed to the event
procedure as Target, which is tested for, and which the Case acts upon.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"CHRIS K" wrote in message
...
How does it know the value
and how does that relate to the select case
--
CHRISK


"Bob Phillips" wrote:

An example

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

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

RP
(remove nothere from the email address if mailing direct)


"CHRIS K" wrote in message
...
how do I get more than 3 conditional formats?
I want to shade the cells depending on the 9 values (criteria range)
each
one a different colour

thanks
--
CHRISK