Is there a maximum number of conditional format rules that can be applied?

[AndyEvans]

Hi All,

I need to add more conditional formatting to my spreadsheet. But it seems to belimiting me to 3. Is there a way of increasing it?

Thanks,

Andy

 

[Flyboy2010]

You should be able to do more than just 3. I have one Excel sheet that has at least 10 conditional formats on one sheet.

 

[AndyEvans]

I would assue so as well. But when I go to the conditional format tool it will not let me add any more. Also I should point out that the 3 are in one cell. I need to add more to that cell.

Andy

 

[sous2817]

In versions earlier than 2007 you can only have 3 conditions in one cell. You'll have to use VBA to go beyond those three.


*edit actually I guess you can have 4 formats if you count the original cell formatting*

 

[Jonmo1]

*edit actually I guess you can have 4 formats if you count the original cell formatting*

Yep, alot of people forget about that...

Also, just to be a little nitpicky...can't help it.

Conditional formatting is NOT limited to 3 conditions.
In fact, the number of conditions you can use is only limited by length of a formula.

Conditional formatting is actually only limited to 3 FORMATs that can be applied.


Example to demonstrate

=OR(A1="Jon",A1="Fred",A1="Barney",A1="Wilma",A1="Betty",A1="Pebbles",A1="BamBam")

That's 7 possible conditions. But only 1 FORMAT would be applied to any of those 7 conditions.


Hope that helps...

 

[VoG]

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

 

[sous2817]

Point well taken, thanks!

 

[AndyEvans]

Thanks VoG.
Got it to sort of work. It works if I physically type the contents of the cell in to the sheet but my spreedsheet uses a vlookup from another sheet. When the figures are imported via the lookup the cells don't change. Any ideas?

Andy

 

[lenze]

A Change Event will not work then.
You will need a different type of code that loops throuight the cells and applies the format. If you post some sample data or explain what you need, we can help you write the code.

lenze

 

[dave3009]

This link was broken for ages but it might be worth a look

http://www.xldynamic.com/source/xld.CFPlus.Download.html

EDIT:Apologies it seems the site is back up but the download links are still broken, my bad sorry