Any equiv. in Conditional Formatting???

Colin Legg · #1 06-05-2012, 02:38 PM

Quote:

But in Excel 2010, it does not work in the conditional formatting formula as it returns something like "cannot use array in conditional formatting formula".

Yes, you're right. To work around that you have to use a name. Go to formulas tab > define name. Create a name called Instructors (or whatever you want to call it) which refers to ={"teacher","tutor"}

Then your conditional formatting rule becomes:
=COUNT(SEARCH(Instructors,$A1))=0

Quote:

Ques 2:
In Column B, I want to know why Cell B2 returns a value error but B4 doesn't? Indeed, I think B2 shouldn't have returned a value error.

In the workbook you attached these cells just have #VALUE! errors - no formulas. So I can't see the original formula / answer this question.

tinfanide · #2 06-06-2012, 07:49 AM

Quote:

Originally Posted by Colin Legg

Yes, you're right. To work around that you have to use a name. Go to formulas tab > define name. Create a name called Instructors (or whatever you want to call it) which refers to ={"teacher","tutor"}

Then your conditional formatting rule becomes:
=COUNT(SEARCH(Instructors,$A1))=0

In the workbook you attached these cells just have #VALUE! errors - no formulas. So I can't see the original formula / answer this question.

Yes, you're right.
I should give a name to the array in 2010.
And you're very specialised in using Excel coz you can tell the little difference between 07 and 10.

And sorry. I don't know why the formula causing the error disappears. I cannot retrieve the formula though. Have to forget them.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Conditional Formatting.	Laurie B.	Excel	6	04-09-2012 05:01 PM
Conditional formatting	zanat0s	Excel	4	01-20-2012 03:30 AM
Conditional formatting with AND, OR	Lucky	Excel	2	10-03-2011 11:41 PM
Conditional Formatting	namedujour	Excel	3	08-25-2011 01:46 PM
help with conditional formatting	Snvlsfoal	Excel	3	07-03-2011 11:55 PM