Conditional formatting with 6 conditions?

I'm using Excel 2000 which has a limit of three conditions, I have 6. If you could just get me started, I still don't understand VBA enough to do this.

In cell A1, the color is set to red. I want A1 to turn green when all 6 conditions are met:

1. Cell B1 has a valid value from its pull down list, no other value, and not empty.
2. Cell B2 has an integer, no decimal places allowed, and not empty.
3. Cell B3 has a valid value from its pull down list, no other value, and not empty
4. Cell B4 is not empty.
5. Cell B5 has a three decimal place number greater than zero.
6. Cell B6 has a single letter from A-Z only.

Please see the attached workbook.

Thank you for your continued help.

There are ample (hundreds of) examples on the forum of formatting via VBA. Have you searched?

Hi, this can be done with just one conditional format, it's just a number of conditions combined in an AND() statement.

Here are the conditions one by one:

=MATCH(B1,names,0)>0
=B2=INT(B2)
=MATCH(B3,cities,0)>0
=AND(B4<>"",B4<>0)
=LEN(B5)-FIND(".",B5)=3
=AND(CODE(UPPER(B6))>=65,CODE(UPPER(B6))<=90,LEN(B6)=1)

And here they are combined into one formula

Please Login or Register  to view this content.

I'm just not sure about B5. It is formatted with three decimals. So would a number entered as 4.44 that is displayed as 4.440 qualify as true or not??

Believe it or not, that is still only 1 conditional formatting formula. Use these settings on cell A1:

1) open Format > Conditional Formatting window
2) Condition1: Formula Is:
=AND(ISNUMBER(MATCH(B1, names, 0)), AND(B2<>"", MOD(B2,1)=0), ISNUMBER(MATCH(B3, cities, 0)), B4<>"", IF(ISNUMBER(FIND(".", B5)), LEN(MID(B5, FIND(".", B5)+1, 99))=3, FALSE), AND(LEN(B6)=1, ISTEXT(B6)))
...Format...Pattern tab....green color

JB, this

...AND(LEN(B6)=1, ISTEXT(B6)))

will also allow entry of characters like "!@#$%^&*()_+" and not restrict to A-Z

Team, there is always room to learn something new. Thanks.


proepert,

Here is a VBA solution using the Worksheet_Change Event.

See the attached workbook "Worksheet_Change Event - Conditional formatting with 6 conditions - proepert - SDG11.xls".

Detach/open the workbook and make changes to range B1:B6.

Thank you all.

I'm struggling to understand the AND command. Yes I have solved my question but still do not fully understand the special characters in the command. Yes I'm going to get a book.

Stan, thank you very much.

proepert,

AND() returns TRUE when all components in it are true.

You want to check if

1. Cell B1 has a valid value from its pull down list, no other value, and not empty.
2. Cell B2 has an integer, no decimal places allowed, and not empty.
3. Cell B3 has a valid value from its pull down list, no other value, and not empty
4. Cell B4 is not empty.
5. Cell B5 has a three decimal place number greater than zero.
6. Cell B6 has a single letter from A-Z only.

individually, these checks can be put into Excel statements that deliver true or false, like

1. =MATCH(B1,names,0)>0
2. =B2=INT(B2)
3. =MATCH(B3,cities,0)>0
4. =AND(B4<>"",B4<>0)
5. =LEN(B5)-FIND(".",B5)=3
6. =AND(CODE(UPPER(B6))>=65,CODE(UPPER(B6))<=90,LEN(B6)=1)

All these six checks return either TRUE or FALSE

You want the conditional format to apply when all six conditions are True, so

=AND(1=TRUE, 2=TRUE, 3=TRUE ... 6=TRUE)

replace each parameter in the mockup AND() statement above with the formulas numbered 1-6, and you'll arrive at this formula: (breaking it down indo individual lines for clarity)

Please Login or Register  to view this content.

Only if all components of the AND() statement evaluate as true, will the AND() function return a TRUE and the conditional format will be applied.

Does that make it clearer?