Hi,

Conditional formatting allows for 3 conditions.

I require 6 for the following ranges of data in a field spannin A1 to G50

0-0.1 pale yellow
0.1-0.25 yellow
0.25-1.0 pale orange
1.0-1.5 orange
1.5-2.0 pale red
2.0 red

I guess an IF, THEN statement might work?

Can anyone help?

Thanks,

Roger

If you are not using XL2007 (and your comment about 3 condition seems to
indicate you are not), then you will need to use VB event code to handle
your 6 conditions. Is a VB solution acceptable?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Hi,

Conditional formatting allows for 3 conditions.

I require 6 for the following ranges of data in a field spannin A1 to G50

0-0.1 pale yellow
0.1-0.25 yellow
0.25-1.0 pale orange
1.0-1.5 orange
1.5-2.0 pale red
2.0 red

I guess an IF, THEN statement might work?

Can anyone help?

Thanks,

Roger

A VB solution would be very satisfactory - do you have some code?

Roger



"Rick Rothstein" wrote:

If you are not using XL2007 (and your comment about 3 condition seems to
indicate you are not), then you will need to use VB event code to handle
your 6 conditions. Is a VB solution acceptable?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Hi,

Conditional formatting allows for 3 conditions.

I require 6 for the following ranges of data in a field spannin A1 to G50

0-0.1 pale yellow
0.1-0.25 yellow
0.25-1.0 pale orange
1.0-1.5 orange
1.5-2.0 pale red
2.0 red

I guess an IF, THEN statement might work?

Can anyone help?

Thanks,

Roger

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "A1:G50" '<=== 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 Is <= 0.1: .Interior.ColorIndex = 36 'pale yellow
Case Is <= 0.25: .Interior.ColorIndex = 6 'yellow
Case Is <= 1: .Interior.ColorIndex = 45 'light
orange
Case Is <= 1.5: .Interior.ColorIndex = 46 'orange
Case Is <= 2: .Interior.ColorIndex = 38 'rose
Case Else: .Interior.ColorIndex = 3 'red
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

"Roger on Excel" wrote in message
...
A VB solution would be very satisfactory - do you have some code?

Roger



"Rick Rothstein" wrote:

If you are not using XL2007 (and your comment about 3 condition seems to
indicate you are not), then you will need to use VB event code to handle
your 6 conditions. Is a VB solution acceptable?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi,

Conditional formatting allows for 3 conditions.

I require 6 for the following ranges of data in a field spannin A1 to
G50

0-0.1 pale yellow
0.1-0.25 yellow
0.25-1.0 pale orange
1.0-1.5 orange
1.5-2.0 pale red
2.0 red

I guess an IF, THEN statement might work?

Can anyone help?

Thanks,

Roger

I don't think you need the EnableEvent nor On Error parts of your code...
changing colors does not kick off a Change event.

--
Rick (MVP - Excel)


"Bob Phillips" wrote in message
...

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "A1:G50" '<=== 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 Is <= 0.1: .Interior.ColorIndex = 36 'pale
yellow
Case Is <= 0.25: .Interior.ColorIndex = 6 'yellow
Case Is <= 1: .Interior.ColorIndex = 45 'light
orange
Case Is <= 1.5: .Interior.ColorIndex = 46 'orange
Case Is <= 2: .Interior.ColorIndex = 38 'rose
Case Else: .Interior.ColorIndex = 3 'red
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

"Roger on Excel" wrote in message
...
A VB solution would be very satisfactory - do you have some code?

Roger



"Rick Rothstein" wrote:

If you are not using XL2007 (and your comment about 3 condition seems to
indicate you are not), then you will need to use VB event code to handle
your 6 conditions. Is a VB solution acceptable?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi,

Conditional formatting allows for 3 conditions.

I require 6 for the following ranges of data in a field spannin A1 to
G50

0-0.1 pale yellow
0.1-0.25 yellow
0.25-1.0 pale orange
1.0-1.5 orange
1.5-2.0 pale red
2.0 red

I guess an IF, THEN statement might work?

Can anyone help?

Thanks,

Roger

Hi Bob,

Ive tried the code, but it doesnt seem to work on my data set - I will try
it on a fresh spreadsheet

Thanks,

Roger

"Bob Phillips" wrote:


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "A1:G50" '<=== 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 Is <= 0.1: .Interior.ColorIndex = 36 'pale yellow
Case Is <= 0.25: .Interior.ColorIndex = 6 'yellow
Case Is <= 1: .Interior.ColorIndex = 45 'light
orange
Case Is <= 1.5: .Interior.ColorIndex = 46 'orange
Case Is <= 2: .Interior.ColorIndex = 38 'rose
Case Else: .Interior.ColorIndex = 3 'red
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

"Roger on Excel" wrote in message
...
A VB solution would be very satisfactory - do you have some code?

Roger



"Rick Rothstein" wrote:

If you are not using XL2007 (and your comment about 3 condition seems to
indicate you are not), then you will need to use VB event code to handle
your 6 conditions. Is a VB solution acceptable?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi,

Conditional formatting allows for 3 conditions.

I require 6 for the following ranges of data in a field spannin A1 to
G50

0-0.1 pale yellow
0.1-0.25 yellow
0.25-1.0 pale orange
1.0-1.5 orange
1.5-2.0 pale red
2.0 red

I guess an IF, THEN statement might work?

Can anyone help?

Thanks,

Roger