Can I use 4 conditonal foamts in a column?

[watermt]

The conditional formatting in Excel 2003 is limited to 3 conditions. I need
four:

Bewteen:
13 - 16 = Red pattern
9 - 12 = Green pattern
5 - 8 = Yellow pattern
1 - 4 = Gray pattern

I thought I ran across a web page at one point where this was talked about
and that it is possible. Does anyone have an idea as to how to do this, or
where I can find a sample?

Mike

 

[Elkar]

If you truly only need 4, then just use the default format as your 4th.
Instead of setting 1-4 to be Grey, just make the cells grey by default, then
use CF for the other conditions.

If, however, you actually need 5 formats (e.g. no format), then this
wouldn't work. You'd need to use VB Code. Set up an WorksheetChange Event,
test the value of each cell, then apply the formats from there.

Or, upgrade to Excel 2007.

HTH
Elkar

 

[Pecoflyer]

Hi,
as you have your default pattern + 3 conditional formats, you have th
4 formats you need.

The conditional formatting in Excel 2003 is limited to 3 conditions.
need
four:

Bewteen:
13 - 16 = Red pattern
9 - 12 = Green pattern
5 - 8 = Yellow pattern
1 - 4 = Gray pattern

I thought I ran across a web page at one point where this was talke
about
and that it is possible. Does anyone have an idea as to how to d
this, or
where I can find a sample?

Mik

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[Mike H]

Hi,

Right click your sheet tab, voew code and paste this in. Change target to
column (currently 1) for column A to your column

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Or Target.Cells.Count > 1 _
Or IsEmpty(Target) Then Exit Sub
Select Case Target.Value
Case 1 To 4
icolor = 15
Case 5 To 8
icolor = 6
Case 9 To 12
icolor = 4
Case 13 To 16
icolor = 3
Case Else
icolor = xlNone
End Select
Target.Interior.ColorIndex = icolor


End Sub

Mike

 

[Luke M]

Use conditional formatting for the green, yellow, and gray conditions.
To get the 4th, go to format cell, custom format, and input:
[Black][>16];[Red][>=14];[Black]

note that if you don't want a default color of black for anything else,
other possible color options are:
[Black] [Blue] [Cyan]
[Green] [Magenta] [Red]
[White] [Yellow]

 

[watermt]

Mike H,
I'm not very good at using VB and am not exactly clear on what you mean by
"change target to column (currently 1) for column A to your column". My
column is F (cells F3 through F20). I tried to replace the number 1 and the
word target in the code with F; then tried F3:F20, and also tried the number
6 (thinking 1 =a, 2=b, 3=c, 4=d, 5=e, 6=f).

But was unsuccessful. Could you clarify for me exactly where in the code I
need to make a change and what do I replace what with?

By the way, I have initially set the cellss font color to white so that the
#N/A does not display when no selections have been made in cells that affect
the color change. I hope this is not a problem with your fix? But, I didn't
want staff freakin' out about seeing the #N/A displayed as a default symbol
in the blank cells.

Sorry about my lack of expertise with Excel and VB,
Mike

 

[watermt]

Luke,
I tried your recommendation, but when I tried to input the
[Black][>16];[Red][>=14];[Black] into the Foramet Cells - Number - Custom
area it gave me an error message. Something about not being able to do this
and to try using one of the provided number formats?

Is there something that I'm missing here?

Mike

Use conditional formatting for the green, yellow, and gray conditions.
To get the 4th, go to format cell, custom format, and input:
[Black][>16];[Red][>=14];[Black]

note that if you don't want a default color of black for anything else,
other possible color options are:
[Black] [Blue] [Cyan]
[Green] [Magenta] [Red]
[White] [Yellow]

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

The conditional formatting in Excel 2003 is limited to 3 conditions. I need
four:

Bewteen:
13 - 16 = Red pattern
9 - 12 = Green pattern
5 - 8 = Yellow pattern
1 - 4 = Gray pattern

I thought I ran across a web page at one point where this was talked about
and that it is possible. Does anyone have an idea as to how to do this, or
where I can find a sample?

Mike

 

[watermt]

Thanks for the info on the Code Cage, just registered, waiting for my email
reply from them. One point I may have failed to mention about my Excel 2003
issue is that I'm running an HLOOKUP formula to display the number in the
cells I want to highlight with red, green, yellow and gray. Each of the
number cells currently display #N/A and the only way I was able to get rid of
that from being displayed was to format the cells for white text. So, if I
use white as a background color the numbers do not display. By the way, my
choice of cell highlighting is not something I just conjured up, it's an
internal compliance issue that I must follow.

Is there a better way to get rid of the #N/A? My HLOOKUP is working as i
intended it to do so!?

Mike

 

[Ashish Mathur]

Hi,

Try this

[Black][>16]General;[Red][>=14]General;

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

Luke,
I tried your recommendation, but when I tried to input the
[Black][>16];[Red][>=14];[Black] into the Foramet Cells - Number - Custom
area it gave me an error message. Something about not being able to do
this
and to try using one of the provided number formats?

Is there something that I'm missing here?

Mike

Use conditional formatting for the green, yellow, and gray conditions.
To get the 4th, go to format cell, custom format, and input:
[Black][>16];[Red][>=14];[Black]

note that if you don't want a default color of black for anything else,
other possible color options are:
[Black] [Blue] [Cyan]
[Green] [Magenta] [Red]
[White] [Yellow]

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

The conditional formatting in Excel 2003 is limited to 3 conditions. I
need
four:

Bewteen:
13 - 16 = Red pattern
9 - 12 = Green pattern
5 - 8 = Yellow pattern
1 - 4 = Gray pattern

I thought I ran across a web page at one point where this was talked
about
and that it is possible. Does anyone have an idea as to how to do
this, or
where I can find a sample?

Mike