Conditional formatting for more than 3 conditions

J

joe show

Could someone shed some light on this subject for me please.
What I have is data is Column I & J from rows 4 thru 45 (I4:J45). I need
the cell to be colored in by the data that is entered in the box.

THis is kind of the bare bones of the arguement:
Condition 1
If Cell Value > 0.1 then Green cell
Condition 2
If cell Value < -0.1 then Red cell
Condition 3
If cell Value between 0.1 & -0.1 then Yellow cell
Condition 4
If cell Value = "mid range" then white (no color)

I have been doing some research and found select case statements to
probably fit the best from what im asking but i havent been able to
create the proper syntax.
Please help anyone that may have an idea how to do this. thanks and very
much appreciate it.


***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
F

Frank Kabel

Hi
conditional format only accepts 3 conditions though you have a fourth
if you include the default format.

If you only want to apply different FONT colors based on NUMBERS, you
can define up to 6 different styles. See:
http://www.mcgimpsey.com/excel/conditional6.html
for instructions how to do it

For everything else you'll need VBA code (e.g. process the
worksheet_change event and apply your format based on the cell values).
The following will color the entry in cell A1:A100 based on its value:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case is > 0.2: .Interior.ColorIndex = 3
Case is < 0.2: .Interior.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

Adapt this to your needs
 
J

joeshow

Frank,
I have followed your advice and implemented the code to the area an
specs I think are needed.
This is what i came up with:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("I1:J45")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case Is > 0.1: .Interior.Color = vbGreen
Case Is < -0.1: .Interior.Color = vbRed
Case is 0.1 to -0.1: .Interior.Color=vbyellow
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

The only problem is that the cells dont seem to change. I have tryed t
update the sheet by restarting it but dont get the cell formatting tha
i need.

Alse the row "Case is 0.1 to -0.1: .Interior.Color=vbyellow" i
highlighted in error.

Do you have any other ideas that will work for this problem??
thank
 
F

Frank Kabel

Hi
first:
this has to go in your worksheet module:
- right-click on your tab name and choose 'code' in the context menu
- paste your code in the appearing editor window
- also change the line
Case is 0.1 to -0.1: .Interior.Color=vbyellow
in
Case 0.1 to -0.1: .Interior.Color=vbyellow
- close the editor and try again
 
J

joeshow

Hi Frank
I changed the code so the error was coming up anymore. But th
spreadsheet doesnt seem to change the cells that should be colored in.
Im wondering if the line: "If Intersect(Target, Me.Range("I1:J45")) I
Nothing Then Exit Sub"

where the part ME.range has to be changed to something particular?
Sorry for the inconvenience and thanks in advance for your help
 
D

Dave Peterson

Did you follow Frank's first piece of advice:

this has to go in your worksheet module:
- right-click on your tab name and choose 'code' in the context menu

Paste it into the correct worksheet's module and it should work ok.
 
D

Dave Peterson

If you're sure you put it behind the correct worksheet, maybe you have macros
disabled.

Tools|Macro|Security|Security Level.
(Don't leave it High in xl2k or higher).

If you changed this, then close excel and reopen excel and your workbook and
make sure you enable macros.

If you didn't have to change this, maybe your .enableevents got turned to off.

Hit ctrl-G in the VBE (where you pasted Frank's code).
type in this and hit enter:

application.enableevents = true

Back to excel to test it out.
 
J

joeshow

Thanks for Frank and Dave,
The only problem that i am really having is that when the cell say
'mid range' the cell shows up as green. So i wondered if theres a wa
to get the cell to not be greened out and just stay white?
what do you think?
PS. if theres a way to avoid using VBA that will incorporate th
Conditional formatting function in XL thats great too.
any thoughts
 
F

Frank Kabel

Hi
if you have only three different conditions yes then you can use
'Format - conditional format. Looking at your changes to the code it
seems 3 conditions are sufficient for you. You may try then the
build-in functionality.

I'm not sure what you mean with 'mid range'. does that mean you
inserted a text. If yes change your code as following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("I1:J45")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
if isnumeric(.value) then
Select Case .Value
Case Is > 0.1: .Interior.Color = vbGreen
Case Is < -0.1: .Interior.Color = vbRed
Case -0.1 to 0.1: .Interior.Color=vbyellow
'etc.
End Select
end if
End With
CleanUp:
Application.EnableEvents = True
End Sub
 
D

David McRitchie

Hi Joe,
I don't think you have indicated whether you are using VBA or
C.F. if you try both, then C.F. will override normal cell formatting.

If you are using C.F. then the conditions are checked in order
condition 1, condition 2, condition 3 as soon as one of them
is true the rest are not examined.

You can use AND(cond1,cond2,...,condn) or OR(cond1,...,condn)
to modify your worksheet type formulas in Conditional Formatting

In VBA you and use AND or OR operators to modify code.

My page on conditional formatting is
http://www.mvps.org/dmcritchie/excel/condfmt.htm
and for where Conditional formatting can't be used because of more than 3 conditions
http://www.mvps.org/dmcritchie/excel/event.htm#case
 
J

joe show

Thanks David
i have deleted the old CF functions that change the cell different
colors. Everything would seem to work fine except the fact that when the
words: 'mid range' come up then the box is green. These cells should be
white and so Im not sure how to change the conditions so that when mid
range comes up its just a white plain cell.
Here is the formula for the cell:
=IF(E12>C15,E12-C15,IF(E12<D15,E12-D15,"mid_range"))

thanks for help everyone.


***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
F

Frank Kabel

Hi Joe
so you're suing the code alternative?. If yes add the lines I provided
in a previous post (you have to check that the cell content is numeric)
 
J

joeshow

Frank,
I tryed putting the code in the sheet module and then putting it in its
own module aswell. I shut down the project and started up again to
refresh the data and still nothing. Maybe this thing is jinx'd.
I wondered about the part in the intercept line that says "Me". what
does "Me" represent. or am I suppose to change this to something
specific to my worksheet?
thanks for all your help.
 
J

joeshow

Hi Frank sorry to bother.
I tryed changing the code and bit but nothing seems to work. I got th
code in a module and that is where its suppose to be right? Do you hav
any more things I could try by chance?

Here is the code i tryed:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("I4:J45")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If IsNumeric(.Value) Then
Select Case .Value
Case Is > 0.1: .Interior.Color = vbGreen
Case Is < -0.1: .Interior.Color = vbRed
Case -0.1 To 0.1: .Interior.Color = vbYellow
'etc.
End Select
End If
End With
CleanUp:
Application.EnableEvents = True
End Su
 
J

joeshow

Hi Frank sorry to bother.
I tryed changing the code and bit but nothing seems to work. I got th
code in a module and that is where its suppose to be right? Do you hav
any more things I could try by chance?

Here is the code i tryed:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("I4:J45")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If IsNumeric(.Value) Then
Select Case .Value
Case Is > 0.1: .Interior.Color = vbGreen
Case Is < -0.1: .Interior.Color = vbRed
Case -0.1 To 0.1: .Interior.Color = vbYellow
'etc.
End Select
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


i also changed the formula so that if the cell doesnt show a postive o
negative result it will say '0' instead of saying 'mid range'.
thought making everything numeric would make things alot easier an
simpiler for the calculation. But still no luck
 
D

Dave Hawley

Hi Joe

Franks code MUST reside in the Private Module of the Sheet Object. To
get there, right click on the sheet name tab (the one you want the
format conditions to apply), select "View Code" and then paste in Franks
code.

The code, 'as is' will ONLY run if the cells that are changed are within
the range I4:J45

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional formatting for more than 3 conditions 3
2nd condition in a conditional formatting not working 9
More than 3 Conditions 2
Conditional formatting if three conditions are true 1
conditional formatting for more than 3 conditions 3
conditional statement help 4
Conditional formatting 4
Conditional formatting puzzle 2

Top