MultiCriteria for Conditional Formating

[Guest]

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.

 

[Guest]

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

Dave

 

[Guest]

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.

 

[Guest]

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

Dave

 

[Bob Phillips]

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

 

[Guest]

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]

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)

 

[Guest]

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]

follow the instructions that I gave you.

--

HTH

Bob Phillips

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

 

[Guest]

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

 

[Guest]

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.

 

[Bob Phillips]

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

 

[Guest]

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]

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)

 

[Radek Simek]

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.