Need more than 2 conditions in Conditional Formatting

Amy · 03-08-2006, 01:55 PM

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

Bob Phillips · 03-08-2006, 03:00 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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 · 03-08-2006, 04:15 PM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

Bob Phillips · 03-09-2006, 06:55 AM

Amy,

can you show the code you entered?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Amy" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

David McRitchie · 03-09-2006, 09:25 AM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 · 03-09-2006, 11:15 AM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

Bob Phillips · 03-09-2006, 02:30 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >