Is there a way to have more than just the 3 conditions in formatting or to
work around it?

I have a range of b4:b35 and I need to highlight cell different colors
depending on cell value..
If cell =0 then fill with red
if cell =1 then fill with yellow
if cell = 2 then fill with green
if cell = 3 then fill with blue
if cell =off then fill with grey

so as of right now I need 5 conditions unless oen of you wonderful experts
out there have an alternative solution for me....

thank you in advance

An example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "B4:B35"

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 Phillips

(remove nothere from email address if mailing direct)

"Amy" wrote in message
...
Is there a way to have more than just the 3 conditions in formatting or to
work around it?

I have a range of b4:b35 and I need to highlight cell different colors
depending on cell value..
If cell =0 then fill with red
if cell =1 then fill with yellow
if cell = 2 then fill with green
if cell = 3 then fill with blue
if cell =off then fill with grey

so as of right now I need 5 conditions unless oen of you wonderful experts
out there have an alternative solution for me....

thank you in advance

Thanks for trying to help Bob but apparently I am doing something incorrect.
I went into the view code option of the sheet, pasted your code into it and
edited it to fit my values amd added 2 additonal and nothing happens. I no
nothing about writing code unfortunately and am at a loss. Any other thoughts?

"Bob Phillips" wrote:

An example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "B4:B35"

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 Phillips

(remove nothere from email address if mailing direct)

"Amy" wrote in message
...
Is there a way to have more than just the 3 conditions in formatting or to
work around it?

I have a range of b4:b35 and I need to highlight cell different colors
depending on cell value..
If cell =0 then fill with red
if cell =1 then fill with yellow
if cell = 2 then fill with green
if cell = 3 then fill with blue
if cell =off then fill with grey

so as of right now I need 5 conditions unless oen of you wonderful experts
out there have an alternative solution for me....

thank you in advance

Amy,

can you show the code you entered?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Amy" wrote in message
...
Thanks for trying to help Bob but apparently I am doing something
incorrect.
I went into the view code option of the sheet, pasted your code into it
and
edited it to fit my values amd added 2 additonal and nothing happens. I no
nothing about writing code unfortunately and am at a loss. Any other
thoughts?

"Bob Phillips" wrote:

An example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "B4:B35"

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 Phillips

(remove nothere from email address if mailing direct)

"Amy" wrote in message
...
Is there a way to have more than just the 3 conditions in formatting
or to
work around it?

I have a range of b4:b35 and I need to highlight cell different colors
depending on cell value..
If cell =0 then fill with red
if cell =1 then fill with yellow
if cell = 2 then fill with green
if cell = 3 then fill with blue
if cell =off then fill with grey

so as of right now I need 5 conditions unless oen of you wonderful
experts
out there have an alternative solution for me....

thank you in advance

Hi Amy,
I think you installed the code as you would an event macro, but it is not an event macro.
Though you could install an event macro that applies to only one sheet, and
would be automatically invoked, example in
http://www.mvps.org/dmcritchie/excel/event.htm#case

The code you got from Bob must go into a module and you might invoke
it from Alt+F8 once installed.
http://www.mvps.org/dmcritchie/excel....htm#havemacro

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

"Amy" wrote in message ...
Thanks for trying to help Bob but apparently I am doing something incorrect.
I went into the view code option of the sheet, pasted your code into it and
edited it to fit my values amd added 2 additonal and nothing happens. I no
nothing about writing code unfortunately and am at a loss. Any other thoughts?

"Bob Phillips" wrote:

An example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "B4:B35"

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 Phillips

(remove nothere from email address if mailing direct)

"Amy" wrote in message
...
Is there a way to have more than just the 3 conditions in formatting or to
work around it?

I have a range of b4:b35 and I need to highlight cell different colors
depending on cell value..
If cell =0 then fill with red
if cell =1 then fill with yellow
if cell = 2 then fill with green
if cell = 3 then fill with blue
if cell =off then fill with grey

so as of right now I need 5 conditions unless oen of you wonderful experts
out there have an alternative solution for me....

thank you in advance

Thank you both. Now that I "invoked" the code Bob gave me (with my data) it
works. Thank you both for your help and follow through.

"Bob Phillips" wrote:

Amy,

can you show the code you entered?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Amy" wrote in message
...
Thanks for trying to help Bob but apparently I am doing something
incorrect.
I went into the view code option of the sheet, pasted your code into it
and
edited it to fit my values amd added 2 additonal and nothing happens. I no
nothing about writing code unfortunately and am at a loss. Any other
thoughts?

"Bob Phillips" wrote:

An example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "B4:B35"

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 Phillips

(remove nothere from email address if mailing direct)

"Amy" wrote in message
...
Is there a way to have more than just the 3 conditions in formatting
or to
work around it?

I have a range of b4:b35 and I need to highlight cell different colors
depending on cell value..
If cell =0 then fill with red
if cell =1 then fill with yellow
if cell = 2 then fill with green
if cell = 3 then fill with blue
if cell =off then fill with grey

so as of right now I need 5 conditions unless oen of you wonderful
experts
out there have an alternative solution for me....

thank you in advance

Well that's odd, because it was event code, and it should have worked as you
entered the data. If the data already existed, you would have needed to edit
it, even without actually changing it, to force the change event.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"amy" wrote in message
...
Thank you both. Now that I "invoked" the code Bob gave me (with my data)
it
works. Thank you both for your help and follow through.

"Bob Phillips" wrote:

Amy,

can you show the code you entered?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Amy" wrote in message
...
Thanks for trying to help Bob but apparently I am doing something
incorrect.
I went into the view code option of the sheet, pasted your code into
it
and
edited it to fit my values amd added 2 additonal and nothing happens.
I no
nothing about writing code unfortunately and am at a loss. Any other
thoughts?

"Bob Phillips" wrote:

An example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "B4:B35"

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 Phillips

(remove nothere from email address if mailing direct)

"Amy" wrote in message
...
Is there a way to have more than just the 3 conditions in
formatting
or to
work around it?

I have a range of b4:b35 and I need to highlight cell different
colors
depending on cell value..
If cell =0 then fill with red
if cell =1 then fill with yellow
if cell = 2 then fill with green
if cell = 3 then fill with blue
if cell =off then fill with grey

so as of right now I need 5 conditions unless oen of you wonderful
experts
out there have an alternative solution for me....

thank you in advance