Conditional formatting II

[Mac]

my condition is of the form AND(A1=B1,A1=C1) - thanks Barb and Pete. But -
instead of just one cell per column, I need to look for A1 in ranges
R1B1:R150B1 and R1C1:R150C1, respectively. What function, or construct,
should I use?

 

[Bob Phillips]

AND(ISNUMBER(MATCH(A1,B1:B150,0)),ISNUMBER(MATCH(A1,C1:C150,0)))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mac]

Excellent! Thank you. One more point - instead of saying explicitly A1, how
do I reference 'this' - value in the current cell? I am going to populate
this formula throughout a large region...

 

[Bernard Liengme]

When you select a range (say A1:A100) and use a Conditional Format formula
such as =A1>10
then it appliers to all selected cells since A1 is a relative address.
So select you range use Bob's formula but make some absolute references as
in
AND(ISNUMBER(MATCH(A1,$B$1:$B$150,0)),ISNUMBER(MATCH(A1,$C$1:$C$150,0)))
and all should be well
best wishes

 

[Mac]

....solved.

Excellent! Thank you. One more point - instead of saying explicitly A1, how
do I reference 'this' - value in the current cell? I am going to populate
this formula throughout a large region...