Multiple Conditions with Conditional Formatting

[bstanton]

Is there a way to have more than 3 conditions for a cell when using conditional formatting? I tried to write code to add more, but I keep getting a run time on the fourth condition. If it's possible, any suggestions on how to make it work? Thank you in advance for any ideas.

 

[Loomah]

if you only want four conditions there's no need for code
if you actually format the cell to a 'default' format you can then use the built in conditional format dialog to apply formats for other scenarios

by way of example, suppose you want to format cells depending on which quartile the value is in:
first set the format you want for a value in the lower quartile range
next use conditinal formatting to apply a format to values greater than the lower quartile and median values
then set formatting for median to upper quartile range
finally set format for values greater than the upper quartile

this may not work in all situations but it's been good for any i've come accross!

however if you need more than 4 conditions or the solution above doesn't work for you then it's down to code and utilising the worksheet change event.

what code do you have so far, if this is the route we need to explore? there are a few limitations and/or complications to this as the change event doesn't fire if the value generated from a formula changes.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

....this thread covers the whole topic in enough detail for just about any circumstance: thread68-223068

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hey b!

You posted a thread here about a week ago, got some good posts, but you have never responded.

What's the deal? Did anything help?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Loomah]

hey geoff, i'm impressed with that! i know you were heavily involved in that thread but to remember it from 7 or 8 years back. i've only ever posted in a fraction of the threads you have and can't remember them from last week!!

and whatever happened to acron? i remember when i first came to tek-tips he was always around the top of the mvps for the forums i was in. him and skip somebody.......

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[bstanton]

SkipVought,

Thanks for checking up, sorry I haven't responded to my earlier post, but your comments did help me tremendously. Once you mentioned it can't be done via the method I was trying to use I decide to rethink my solution. What I decided to do, which worked fairly well, was set up dynamic page breaks in the code that moved depending on what the user had selected to print. I'm not sure if it’s the most efficient way to do things, but for someone like me just learning the ropes of VBA I consider it a minor victory.
As for my current dilemma, I haven’t checked out what xlbo has posted, but definitely intend to. I think I have a creative solution to combine some of the conditional formatting to free up other conditions.
Thanks everyone for all of your help; you guys have really helped my project progress. Thanks again.

 

[xlbo]

Hey Loomah - it's in my archived threads list as I must've referenced it about 20 times since!

No idea what happened to acron - sometimes people just drift off....I know I've been a lot less regular here than I used to be mainly because of my job becoming a lot more time intense!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question