Conditional Format Limitation

This may help http://www.vbaexpress.com/kb/getarticle.php?kb_id=90

Set up your first 3 conditions for red, amber and green. Then shade the cells purple - all those that don't meet one of the conditions will stay as purple, thereby acting like a 4th condition.

manyo23 said:

Hi I am working on Microsoft Office Standard Edition 2003 excel and am having trouble with my coniditional formats. I need to have 4 but can only have 3. Is there a simplae way around this? if not let me describe what i want to do and maybe there is a coded solution.

In columns N, R, V and Z i will have % increase in sales over a period. Below 0% would be red, between 0-5% is Amber, 5-10 is Green and over 10% is Purple. Is there a simple code that can be applied to this?

I can define the paramteres in excel in the row columns below. Any ideas? Thanks alot in advance.

A B C
1 Range1 Range2
2 red 0%
3 amber 0% 5%
4 green 5% 10%
5 purple 10%

Click to expand...

If you have 4 conditions and all the data fits into one of the four conditions, then you can do something like this:
1. Colour all the cells red manually with direct cell formatting.
2. Now apply 3 Conditional Formatting rules for the Amber, Green and Purple conditions

Edit: Sorry, didn't see Neil's post - same idea.

Hi Guys, thanks for the qucik come back, but unfortunely i can't colour in the entire row and then that have as my 4th default. I have had a loook at VOGS reply and unfortunatly I am not even a basic at VBA. Is ethere a more simlified code that i can follow or would there be a code with exlnantions as to how i can edit the code around so it meets my personal needs?

Try this: right click the sheet tab, select View Code and paste in


Press ALT + Q to return to your sheet and try changing some values.

In case more than one cell might be changed at once, so that the code doesn't error out and does deal with all the changed cells, I would suggest this modification:

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> icol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br><br>    <SPAN style="color:#00007F">Set</SPAN> myRange = Intersect(Target, Range("N:N, R:R, V:V, Z:Z"))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> myRange <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> myRange<br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> c.Value<br>                <SPAN style="color:#00007F">Case</SPAN> "": icol = xlNone<br>                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 0: icol = 3<br>                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <= 0.05: icol = 45<br>                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <= 0.1: icol = 4<br>                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> > 0.1: icol = 7<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>            c.Interior.ColorIndex = icol<br>        <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

Superstars - Thank you

Hi Guys,

I have been using the code below. I have raw data in column m to aa. I am then runnign simple avegare formulas in column h to l. So i would populate 20 or 30 columns with raw data on m3:aa30, i then have formaule on m3:aa3 that i wish to drag down and of course i would like the colours of the stats to show how well, bad they have done, but it keeps on coming up with debug error. i gnore the debug error and none of the colours have changed. I then have to press f2 enter for the clours to come out. can you help me with this problem so that the colours come out automatically without the error. (i fid try and press f9 but with no luck).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icol As Integer
If Target.Column = 8 Or Target.Column = 9 Or Target.Column = 10 Or Target.Column = 11 Or Target.Column = 12 Then
Select Case Target.Value
Case Is < 0: icol = 3
Case Is <= 0.05: icol = 45
Case Is <= 0.1: icol = 4
Case Is > 0.1: icol = 7
End Select
Target.Interior.ColorIndex = icol
End If
End Sub

manyo23 said:

Hi Guys,

I have been using the code below. I have raw data in column m to aa. I am then runnign simple avegare formulas in column h to l. So i would populate 20 or 30 columns with raw data on m3:aa30, i then have formaule on m3:aa3 that i wish to drag down and of course i would like the colours of the stats to show how well, bad they have done, but it keeps on coming up with debug error. i gnore the debug error and none of the colours have changed. I then have to press f2 enter for the clours to come out. can you help me with this problem so that the colours come out automatically without the error. (i fid try and press f9 but with no luck).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icol As Integer
If Target.Column = 8 Or Target.Column = 9 Or Target.Column = 10 Or Target.Column = 11 Or Target.Column = 12 Then
Select Case Target.Value
Case Is < 0: icol = 3
Case Is <= 0.05: icol = 45
Case Is <= 0.1: icol = 4
Case Is > 0.1: icol = 7
End Select
Target.Interior.ColorIndex = icol
End If
End Sub

Click to expand...

I think your problem relates to you changing more than one cell at a time, which is what my previous comment was aimed at:

In case more than one cell might be changed at once, so that the code doesn't error out ...

Click to expand...

If more than one cell is changed at once, then 'Target.Value' is meaningless and causes the error. Try a code structure like I suggested above.