I have 4 criteria for conditional formatting as follows: I have 4 work crews.
1=green, 2=blue, 3=tan, 4=yellow. If I type a 4 in the cell I want the fill
color to become yellow etc. I obviously can only do three - How can I do 4.
Thanks.

Either write a VBA routine or use XL 2007. Previous versions limit you to 3
conditional formatting criteria.

Dave
--
Brevity is the soul of wit.


"Kim Shelton at PDC" wrote:

I have 4 criteria for conditional formatting as follows: I have 4 work crews.
1=green, 2=blue, 3=tan, 4=yellow. If I type a 4 in the cell I want the fill
color to become yellow etc. I obviously can only do three - How can I do 4.
Thanks.

Dave, Since I don't have XL 2007 I guess I will need to write a VBA routine
however, I have no idea what you are talking about. Could use please explain
so that I can learn and write one. Thanks.

"Dave F" wrote:

Either write a VBA routine or use XL 2007. Previous versions limit you to 3
conditional formatting criteria.

Dave
--
Brevity is the soul of wit.


"Kim Shelton at PDC" wrote:

I have 4 criteria for conditional formatting as follows: I have 4 work crews.
1=green, 2=blue, 3=tan, 4=yellow. If I type a 4 in the cell I want the fill
color to become yellow etc. I obviously can only do three - How can I do 4.
Thanks.

Here's some info on creating a VBA routine which avoids the 3 condition
limit: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm

Dave
--
Brevity is the soul of wit.


"Kim Shelton at PDC" wrote:

Dave, Since I don't have XL 2007 I guess I will need to write a VBA routine
however, I have no idea what you are talking about. Could use please explain
so that I can learn and write one. Thanks.

"Dave F" wrote:

Either write a VBA routine or use XL 2007. Previous versions limit you to 3
conditional formatting criteria.

Dave
--
Brevity is the soul of wit.


"Kim Shelton at PDC" wrote:

I have 4 criteria for conditional formatting as follows: I have 4 work crews.
1=green, 2=blue, 3=tan, 4=yellow. If I type a 4 in the cell I want the fill
color to become yellow etc. I obviously can only do three - How can I do 4.
Thanks.

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== 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 1: .Interior.ColorIndex = 10 'green
Case 2: .Interior.ColorIndex = 5 'blue
Case 40: .Interior.ColorIndex = 3 'tan
Case 4: .Interior.ColorIndex = 6 'yellow
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

(replace xxxx in the email address with gmail if mailing direct)

"Kim Shelton at PDC" wrote in
message ...
I have 4 criteria for conditional formatting as follows: I have 4 work
crews.
1=green, 2=blue, 3=tan, 4=yellow. If I type a 4 in the cell I want the
fill
color to become yellow etc. I obviously can only do three - How can I do
4.
Thanks.

Thanks Bob, I have pasted everything that you have into a test spreadsheet
and have A1:A10 in the range where you pointed. I have on the actual
spreadsheet 1 in A1, 2 in A2, 3 in A3 and 4 in A4. But nothing happens.
What am I doing wrong?

Additionally, So that I can understand, What is the Case 1: mean is this
the criteria? and what does .interior.colorindex = 10- 'green is this the
color command portion of this formula???? I am asking so that I can learn
what I am doing.

"Bob Phillips" wrote:

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== 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 1: .Interior.ColorIndex = 10 'green
Case 2: .Interior.ColorIndex = 5 'blue
Case 40: .Interior.ColorIndex = 3 'tan
Case 4: .Interior.ColorIndex = 6 'yellow
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

(replace xxxx in the email address with gmail if mailing direct)

"Kim Shelton at PDC" wrote in
message ...
I have 4 criteria for conditional formatting as follows: I have 4 work
crews.
1=green, 2=blue, 3=tan, 4=yellow. If I type a 4 in the cell I want the
fill
color to become yellow etc. I obviously can only do three - How can I do
4.
Thanks.

Case 1 is the value that is being tested. This assumes that the values being
tested is a number, put it in quotes if is a string.

..interior.colorindex is where the colour is set. Check the Colorindex
Property in VBA help to see what the values are.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Kim Shelton at PDC" wrote in
message ...
Thanks Bob, I have pasted everything that you have into a test
spreadsheet
and have A1:A10 in the range where you pointed. I have on the actual
spreadsheet 1 in A1, 2 in A2, 3 in A3 and 4 in A4. But nothing happens.
What am I doing wrong?

Additionally, So that I can understand, What is the Case 1: mean is this
the criteria? and what does .interior.colorindex = 10- 'green is this the
color command portion of this formula???? I am asking so that I can learn
what I am doing.

"Bob Phillips" wrote:

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== 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 1: .Interior.ColorIndex = 10 'green
Case 2: .Interior.ColorIndex = 5 'blue
Case 40: .Interior.ColorIndex = 3 'tan
Case 4: .Interior.ColorIndex = 6 'yellow
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

(replace xxxx in the email address with gmail if mailing direct)

"Kim Shelton at PDC" wrote
in
message ...
I have 4 criteria for conditional formatting as follows: I have 4
work
crews.
1=green, 2=blue, 3=tan, 4=yellow. If I type a 4 in the cell I want
the
fill
color to become yellow etc. I obviously can only do three - How can
I do
4.
Thanks.

Hi, I still cant get this to work. How and Where do I tell case value 1 = 1
so that it will turn to the color selected?

"Bob Phillips" wrote:

Case 1 is the value that is being tested. This assumes that the values being
tested is a number, put it in quotes if is a string.

..interior.colorindex is where the colour is set. Check the Colorindex
Property in VBA help to see what the values are.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Kim Shelton at PDC" wrote in
message ...
Thanks Bob, I have pasted everything that you have into a test
spreadsheet
and have A1:A10 in the range where you pointed. I have on the actual
spreadsheet 1 in A1, 2 in A2, 3 in A3 and 4 in A4. But nothing happens.
What am I doing wrong?

Additionally, So that I can understand, What is the Case 1: mean is this
the criteria? and what does .interior.colorindex = 10- 'green is this the
color command portion of this formula???? I am asking so that I can learn
what I am doing.

"Bob Phillips" wrote:

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== 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 1: .Interior.ColorIndex = 10 'green
Case 2: .Interior.ColorIndex = 5 'blue
Case 40: .Interior.ColorIndex = 3 'tan
Case 4: .Interior.ColorIndex = 6 'yellow
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

(replace xxxx in the email address with gmail if mailing direct)

"Kim Shelton at PDC" wrote
in
message ...
I have 4 criteria for conditional formatting as follows: I have 4
work
crews.
1=green, 2=blue, 3=tan, 4=yellow. If I type a 4 in the cell I want
the
fill
color to become yellow etc. I obviously can only do three - How can
I do
4.
Thanks.

follow the instructions that I gave you.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Kim Shelton at PDC" wrote in
message ...
Hi, I still cant get this to work. How and Where do I tell case value 1 =
1
so that it will turn to the color selected?

"Bob Phillips" wrote:

Case 1 is the value that is being tested. This assumes that the values
being
tested is a number, put it in quotes if is a string.

..interior.colorindex is where the colour is set. Check the Colorindex
Property in VBA help to see what the values are.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Kim Shelton at PDC" wrote
in
message ...
Thanks Bob, I have pasted everything that you have into a test
spreadsheet
and have A1:A10 in the range where you pointed. I have on the actual
spreadsheet 1 in A1, 2 in A2, 3 in A3 and 4 in A4. But nothing
happens.
What am I doing wrong?

Additionally, So that I can understand, What is the Case 1: mean is
this
the criteria? and what does .interior.colorindex = 10- 'green is this
the
color command portion of this formula???? I am asking so that I can
learn
what I am doing.

"Bob Phillips" wrote:

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== 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 1: .Interior.ColorIndex = 10 'green
Case 2: .Interior.ColorIndex = 5 'blue
Case 40: .Interior.ColorIndex = 3 'tan
Case 4: .Interior.ColorIndex = 6 'yellow
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

(replace xxxx in the email address with gmail if mailing direct)

"Kim Shelton at PDC"
wrote
in
message ...
I have 4 criteria for conditional formatting as follows: I have 4
work
crews.
1=green, 2=blue, 3=tan, 4=yellow. If I type a 4 in the cell I
want
the
fill
color to become yellow etc. I obviously can only do three - How
can
I do
4.
Thanks.

Kim,

Before applying any conditional formatting give all the cells a green
formatting as if they are all crew 1. Now you can apply three conditional
formats for crews 2, 3 and 4.

Of course, this assumes that all cells will have a work crew in them. If you
need them to be blank then I am afraid you are left with a VBA solution.

Carl

"Kim Shelton at PDC" wrote:

I have 4 criteria for conditional formatting as follows: I have 4 work crews.
1=green, 2=blue, 3=tan, 4=yellow. If I type a 4 in the cell I want the fill
color to become yellow etc. I obviously can only do three - How can I do 4.
Thanks.

Thanks Carl, Yes, I say your info in other questions. I really don't want
to do that because I will have blank cells. But I can't get the VBA solution
to work. I did follow the other instructions posted but I couldn't get them
to work -- If you would be so kind as to explain the VBA solution in a way
that I can understand and get it to work that would be wonderful. I would
really appreciate it.

"CarlSprake" wrote:

Kim,

Before applying any conditional formatting give all the cells a green
formatting as if they are all crew 1. Now you can apply three conditional
formats for crews 2, 3 and 4.

Of course, this assumes that all cells will have a work crew in them. If you
need them to be blank then I am afraid you are left with a VBA solution.

Carl

"Kim Shelton at PDC" wrote:

I have 4 criteria for conditional formatting as follows: I have 4 work crews.
1=green, 2=blue, 3=tan, 4=yellow. If I type a 4 in the cell I want the fill
color to become yellow etc. I obviously can only do three - How can I do 4.
Thanks.

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" ' <=== 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 1: .Interior.ColorIndex = 10 'green
Case 2: .Interior.ColorIndex = 5 'blue
Case 3: .Interior.ColorIndex = 40 'tan
Case 4: .Interior.ColorIndex = 6 'yellow
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

(replace somewhere in email address with gmail if mailing direct)

"Kim Shelton at PDC" wrote in
message ...
Thanks Carl, Yes, I say your info in other questions. I really don't
want
to do that because I will have blank cells. But I can't get the VBA
solution
to work. I did follow the other instructions posted but I couldn't get
them
to work -- If you would be so kind as to explain the VBA solution in a way
that I can understand and get it to work that would be wonderful. I would
really appreciate it.

"CarlSprake" wrote:

Kim,

Before applying any conditional formatting give all the cells a green
formatting as if they are all crew 1. Now you can apply three
conditional
formats for crews 2, 3 and 4.

Of course, this assumes that all cells will have a work crew in them. If
you
need them to be blank then I am afraid you are left with a VBA solution.

Carl

"Kim Shelton at PDC" wrote:

I have 4 criteria for conditional formatting as follows: I have 4
work crews.
1=green, 2=blue, 3=tan, 4=yellow. If I type a 4 in the cell I want
the fill
color to become yellow etc. I obviously can only do three - How can
I do 4.
Thanks.

Thanks, but as I have previously said to you that even though I have
"followed your instructions" and pasted this code in the view code of my test
spreadsheet with my crew numbers in the cell H1:h10 nothing happens. I don't
know what I am doing and previously needed more information to actually get
this to work. So if you would like to help me please explain what could
possible be problems that would cause the conditional formatting not to work.

"Bob Phillips" wrote:

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" ' <=== 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 1: .Interior.ColorIndex = 10 'green
Case 2: .Interior.ColorIndex = 5 'blue
Case 3: .Interior.ColorIndex = 40 'tan
Case 4: .Interior.ColorIndex = 6 'yellow
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

(replace somewhere in email address with gmail if mailing direct)

"Kim Shelton at PDC" wrote in
message ...
Thanks Carl, Yes, I say your info in other questions. I really don't
want
to do that because I will have blank cells. But I can't get the VBA
solution
to work. I did follow the other instructions posted but I couldn't get
them
to work -- If you would be so kind as to explain the VBA solution in a way
that I can understand and get it to work that would be wonderful. I would
really appreciate it.

"CarlSprake" wrote:

Kim,

Before applying any conditional formatting give all the cells a green
formatting as if they are all crew 1. Now you can apply three
conditional
formats for crews 2, 3 and 4.

Of course, this assumes that all cells will have a work crew in them. If
you
need them to be blank then I am afraid you are left with a VBA solution.

Carl

"Kim Shelton at PDC" wrote:

I have 4 criteria for conditional formatting as follows: I have 4
work crews.
1=green, 2=blue, 3=tan, 4=yellow. If I type a 4 in the cell I want
the fill
color to become yellow etc. I obviously can only do three - How can
I do 4.
Thanks.

All the information is there, I don't know what else I can add.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Kim Shelton at PDC" wrote in
message ...
Thanks, but as I have previously said to you that even though I have
"followed your instructions" and pasted this code in the view code of my
test
spreadsheet with my crew numbers in the cell H1:h10 nothing happens. I
don't
know what I am doing and previously needed more information to actually
get
this to work. So if you would like to help me please explain what could
possible be problems that would cause the conditional formatting not to
work.

"Bob Phillips" wrote:

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" ' <=== 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 1: .Interior.ColorIndex = 10 'green
Case 2: .Interior.ColorIndex = 5 'blue
Case 3: .Interior.ColorIndex = 40 'tan
Case 4: .Interior.ColorIndex = 6 'yellow
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

(replace somewhere in email address with gmail if mailing direct)

"Kim Shelton at PDC" wrote
in
message ...
Thanks Carl, Yes, I say your info in other questions. I really don't
want
to do that because I will have blank cells. But I can't get the VBA
solution
to work. I did follow the other instructions posted but I couldn't
get
them
to work -- If you would be so kind as to explain the VBA solution in a
way
that I can understand and get it to work that would be wonderful. I
would
really appreciate it.

"CarlSprake" wrote:

Kim,

Before applying any conditional formatting give all the cells a
green
formatting as if they are all crew 1. Now you can apply three
conditional
formats for crews 2, 3 and 4.

Of course, this assumes that all cells will have a work crew in
them. If
you
need them to be blank then I am afraid you are left with a VBA
solution.

Carl

"Kim Shelton at PDC" wrote:

I have 4 criteria for conditional formatting as follows: I have 4
work crews.
1=green, 2=blue, 3=tan, 4=yellow. If I type a 4 in the cell I
want
the fill
color to become yellow etc. I obviously can only do three - How
can
I do 4.
Thanks.

Hi Kim,

if you have only 4 criteria, use simple formating for all fields you want to
format conditionally. Then add typical conditional formating (only for three
criteria). The fourth option will remain with original format.



"Kim Shelton at PDC" wrote:

I have 4 criteria for conditional formatting as follows: I have 4 work crews.
1=green, 2=blue, 3=tan, 4=yellow. If I type a 4 in the cell I want the fill
color to become yellow etc. I obviously can only do three - How can I do 4.
Thanks.