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 = quot;B4:B35quot;
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)
quot;Amyquot; gt; wrote in message
...
gt; Is there a way to have more than just the 3 conditions in formatting or to
gt; work around it?
gt;
gt; I have a range of b4:b35 and I need to highlight cell different colors
gt; depending on cell value..
gt; If cell =0 then fill with red
gt; if cell =1 then fill with yellow
gt; if cell = 2 then fill with green
gt; if cell = 3 then fill with blue
gt; if cell =off then fill with grey
gt;
gt; so as of right now I need 5 conditions unless oen of you wonderful experts
gt; out there have an alternative solution for me....
gt;
gt; 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?
quot;Bob Phillipsquot; wrote:
gt; An example
gt;
gt;
gt; '-----------------------------------------------------------------
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; '-----------------------------------------------------------------
gt; Const WS_RANGE As String = quot;B4:B35quot;
gt;
gt; On Error GoTo ws_exit:
gt; Application.EnableEvents = False
gt; If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
gt; With Target
gt; Select Case .Value
gt; Case 1: .Interior.ColorIndex = 3 'red
gt; Case 2: .Interior.ColorIndex = 6 'yellow
gt; Case 3: .Interior.ColorIndex = 5 'blue
gt; Case 4: .Interior.ColorIndex = 10 'green
gt; End Select
gt; End With
gt; End If
gt;
gt; ws_exit:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; 'This is worksheet event code, which means that it needs to be
gt; 'placed in the appropriate worksheet code module, not a standard
gt; 'code module. To do this, right-click on the sheet tab, select
gt; 'the View Code option from the menu, and paste the code in.
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Amyquot; gt; wrote in message
gt; ...
gt; gt; Is there a way to have more than just the 3 conditions in formatting or to
gt; gt; work around it?
gt; gt;
gt; gt; I have a range of b4:b35 and I need to highlight cell different colors
gt; gt; depending on cell value..
gt; gt; If cell =0 then fill with red
gt; gt; if cell =1 then fill with yellow
gt; gt; if cell = 2 then fill with green
gt; gt; if cell = 3 then fill with blue
gt; gt; if cell =off then fill with grey
gt; gt;
gt; gt; so as of right now I need 5 conditions unless oen of you wonderful experts
gt; gt; out there have an alternative solution for me....
gt; gt;
gt; gt; thank you in advance
gt;
gt;
gt;
Amy,
can you show the code you entered?
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;Amyquot; gt; wrote in message
...
gt; Thanks for trying to help Bob but apparently I am doing something
incorrect.
gt; I went into the view code option of the sheet, pasted your code into it
and
gt; edited it to fit my values amd added 2 additonal and nothing happens. I no
gt; nothing about writing code unfortunately and am at a loss. Any other
thoughts?
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; An example
gt; gt;
gt; gt;
gt; gt; '-----------------------------------------------------------------
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; '-----------------------------------------------------------------
gt; gt; Const WS_RANGE As String = quot;B4:B35quot;
gt; gt;
gt; gt; On Error GoTo ws_exit:
gt; gt; Application.EnableEvents = False
gt; gt; If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
gt; gt; With Target
gt; gt; Select Case .Value
gt; gt; Case 1: .Interior.ColorIndex = 3 'red
gt; gt; Case 2: .Interior.ColorIndex = 6 'yellow
gt; gt; Case 3: .Interior.ColorIndex = 5 'blue
gt; gt; Case 4: .Interior.ColorIndex = 10 'green
gt; gt; End Select
gt; gt; End With
gt; gt; End If
gt; gt;
gt; gt; ws_exit:
gt; gt; Application.EnableEvents = True
gt; gt; End Sub
gt; gt;
gt; gt; 'This is worksheet event code, which means that it needs to be
gt; gt; 'placed in the appropriate worksheet code module, not a standard
gt; gt; 'code module. To do this, right-click on the sheet tab, select
gt; gt; 'the View Code option from the menu, and paste the code in.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Amyquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Is there a way to have more than just the 3 conditions in formatting
or to
gt; gt; gt; work around it?
gt; gt; gt;
gt; gt; gt; I have a range of b4:b35 and I need to highlight cell different colors
gt; gt; gt; depending on cell value..
gt; gt; gt; If cell =0 then fill with red
gt; gt; gt; if cell =1 then fill with yellow
gt; gt; gt; if cell = 2 then fill with green
gt; gt; gt; if cell = 3 then fill with blue
gt; gt; gt; if cell =off then fill with grey
gt; gt; gt;
gt; gt; gt; so as of right now I need 5 conditions unless oen of you wonderful
experts
gt; gt; gt; out there have an alternative solution for me....
gt; gt; gt;
gt; gt; gt; thank you in advance
gt; gt;
gt; gt;
gt; gt;
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
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.
www.mvps.org/dmcritchie/excel....htm#havemacro
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm
quot;Amyquot; gt; wrote in message ...
gt; Thanks for trying to help Bob but apparently I am doing something incorrect.
gt; I went into the view code option of the sheet, pasted your code into it and
gt; edited it to fit my values amd added 2 additonal and nothing happens. I no
gt; nothing about writing code unfortunately and am at a loss. Any other thoughts?
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; An example
gt; gt;
gt; gt;
gt; gt; '-----------------------------------------------------------------
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; '-----------------------------------------------------------------
gt; gt; Const WS_RANGE As String = quot;B4:B35quot;
gt; gt;
gt; gt; On Error GoTo ws_exit:
gt; gt; Application.EnableEvents = False
gt; gt; If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
gt; gt; With Target
gt; gt; Select Case .Value
gt; gt; Case 1: .Interior.ColorIndex = 3 'red
gt; gt; Case 2: .Interior.ColorIndex = 6 'yellow
gt; gt; Case 3: .Interior.ColorIndex = 5 'blue
gt; gt; Case 4: .Interior.ColorIndex = 10 'green
gt; gt; End Select
gt; gt; End With
gt; gt; End If
gt; gt;
gt; gt; ws_exit:
gt; gt; Application.EnableEvents = True
gt; gt; End Sub
gt; gt;
gt; gt; 'This is worksheet event code, which means that it needs to be
gt; gt; 'placed in the appropriate worksheet code module, not a standard
gt; gt; 'code module. To do this, right-click on the sheet tab, select
gt; gt; 'the View Code option from the menu, and paste the code in.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Amyquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Is there a way to have more than just the 3 conditions in formatting or to
gt; gt; gt; work around it?
gt; gt; gt;
gt; gt; gt; I have a range of b4:b35 and I need to highlight cell different colors
gt; gt; gt; depending on cell value..
gt; gt; gt; If cell =0 then fill with red
gt; gt; gt; if cell =1 then fill with yellow
gt; gt; gt; if cell = 2 then fill with green
gt; gt; gt; if cell = 3 then fill with blue
gt; gt; gt; if cell =off then fill with grey
gt; gt; gt;
gt; gt; gt; so as of right now I need 5 conditions unless oen of you wonderful experts
gt; gt; gt; out there have an alternative solution for me....
gt; gt; gt;
gt; gt; gt; thank you in advance
gt; gt;
gt; gt;
gt; gt;
Thank you both. Now that I quot;invokedquot; the code Bob gave me (with my data) it
works. Thank you both for your help and follow through.
quot;Bob Phillipsquot; wrote:
gt; Amy,
gt;
gt; can you show the code you entered?
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Amyquot; gt; wrote in message
gt; ...
gt; gt; Thanks for trying to help Bob but apparently I am doing something
gt; incorrect.
gt; gt; I went into the view code option of the sheet, pasted your code into it
gt; and
gt; gt; edited it to fit my values amd added 2 additonal and nothing happens. I no
gt; gt; nothing about writing code unfortunately and am at a loss. Any other
gt; thoughts?
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; An example
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; '-----------------------------------------------------------------
gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; gt; '-----------------------------------------------------------------
gt; gt; gt; Const WS_RANGE As String = quot;B4:B35quot;
gt; gt; gt;
gt; gt; gt; On Error GoTo ws_exit:
gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
gt; gt; gt; With Target
gt; gt; gt; Select Case .Value
gt; gt; gt; Case 1: .Interior.ColorIndex = 3 'red
gt; gt; gt; Case 2: .Interior.ColorIndex = 6 'yellow
gt; gt; gt; Case 3: .Interior.ColorIndex = 5 'blue
gt; gt; gt; Case 4: .Interior.ColorIndex = 10 'green
gt; gt; gt; End Select
gt; gt; gt; End With
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; ws_exit:
gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; 'This is worksheet event code, which means that it needs to be
gt; gt; gt; 'placed in the appropriate worksheet code module, not a standard
gt; gt; gt; 'code module. To do this, right-click on the sheet tab, select
gt; gt; gt; 'the View Code option from the menu, and paste the code in.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Amyquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Is there a way to have more than just the 3 conditions in formatting
gt; or to
gt; gt; gt; gt; work around it?
gt; gt; gt; gt;
gt; gt; gt; gt; I have a range of b4:b35 and I need to highlight cell different colors
gt; gt; gt; gt; depending on cell value..
gt; gt; gt; gt; If cell =0 then fill with red
gt; gt; gt; gt; if cell =1 then fill with yellow
gt; gt; gt; gt; if cell = 2 then fill with green
gt; gt; gt; gt; if cell = 3 then fill with blue
gt; gt; gt; gt; if cell =off then fill with grey
gt; gt; gt; gt;
gt; gt; gt; gt; so as of right now I need 5 conditions unless oen of you wonderful
gt; experts
gt; gt; gt; gt; out there have an alternative solution for me....
gt; gt; gt; gt;
gt; gt; gt; gt; thank you in advance
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;
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)
quot;amyquot; gt; wrote in message
...
gt; Thank you both. Now that I quot;invokedquot; the code Bob gave me (with my data)
it
gt; works. Thank you both for your help and follow through.
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Amy,
gt; gt;
gt; gt; can you show the code you entered?
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Amyquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Thanks for trying to help Bob but apparently I am doing something
gt; gt; incorrect.
gt; gt; gt; I went into the view code option of the sheet, pasted your code into
it
gt; gt; and
gt; gt; gt; edited it to fit my values amd added 2 additonal and nothing happens.
I no
gt; gt; gt; nothing about writing code unfortunately and am at a loss. Any other
gt; gt; thoughts?
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; An example
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; '-----------------------------------------------------------------
gt; gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; gt; gt; '-----------------------------------------------------------------
gt; gt; gt; gt; Const WS_RANGE As String = quot;B4:B35quot;
gt; gt; gt; gt;
gt; gt; gt; gt; On Error GoTo ws_exit:
gt; gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; gt; If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
gt; gt; gt; gt; With Target
gt; gt; gt; gt; Select Case .Value
gt; gt; gt; gt; Case 1: .Interior.ColorIndex = 3 'red
gt; gt; gt; gt; Case 2: .Interior.ColorIndex = 6 'yellow
gt; gt; gt; gt; Case 3: .Interior.ColorIndex = 5 'blue
gt; gt; gt; gt; Case 4: .Interior.ColorIndex = 10 'green
gt; gt; gt; gt; End Select
gt; gt; gt; gt; End With
gt; gt; gt; gt; End If
gt; gt; gt; gt;
gt; gt; gt; gt; ws_exit:
gt; gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; 'This is worksheet event code, which means that it needs to be
gt; gt; gt; gt; 'placed in the appropriate worksheet code module, not a standard
gt; gt; gt; gt; 'code module. To do this, right-click on the sheet tab, select
gt; gt; gt; gt; 'the View Code option from the menu, and paste the code in.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Amyquot; gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; Is there a way to have more than just the 3 conditions in
formatting
gt; gt; or to
gt; gt; gt; gt; gt; work around it?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I have a range of b4:b35 and I need to highlight cell different
colors
gt; gt; gt; gt; gt; depending on cell value..
gt; gt; gt; gt; gt; If cell =0 then fill with red
gt; gt; gt; gt; gt; if cell =1 then fill with yellow
gt; gt; gt; gt; gt; if cell = 2 then fill with green
gt; gt; gt; gt; gt; if cell = 3 then fill with blue
gt; gt; gt; gt; gt; if cell =off then fill with grey
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; so as of right now I need 5 conditions unless oen of you wonderful
gt; gt; experts
gt; gt; gt; gt; gt; out there have an alternative solution for me....
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; thank you in advance
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
留言列表
