If any cell in group matches any cell in another group

[mjones]

Hi All,

I have a range C6 to AK6 where some have x's in them. There are five
groups of x's. R6 to T6 is the smallest group.

I'd like S6 in the group to turn colour if, any cell in C6 to AK6
matches it (has an x), except the cells in it's group (R6 to T6).

Does anyone know how to do this first part?

Then the hard one is the non-contiguous group. It's C6 to E6, plus R6,
U6, and AA6. Yes, this group has four cells which are in other groups,
too.

Does anyone know how to do this part 2?

I thought conditional formating would work, but I'm not very clear on
how the formula part works.

XP and Excel 2003, but has to work on 2002, too. I hope that's enough
info.

Thanks,

Michele

 

[mjones]

Actually, that's a bad title, sorry. It's confusing to say 'any' cell
in group and should say 'a' cell.

 

[PCLIVE]

In conditional formatting, select formula is. Then use this formula.
=IF(COUNTIF(C6:Q6,K7)+COUNTIF(U6:AK6,K7)>0,TRUE)
That should accomplish your first part.

I'm not sure what you want to do in the second part. Are you still checking
for an "x" in these cells too? It's unclear because C6 is part of your
first part...but R6 was stated to be an exception.

HTH,
Paul

 

[mjones]

Got it! Thanks, Paul.

I made A1 an x to look for. So my formula for part a is:

=IF(COUNTIF($C6:$T6,$A$1)+COUNTIF($AA6:$AK6,$A$1)>0,TRUE)

Yes, part 2 also looks for x's. The first row would looke for x's in
F6 to Q6, plus S6 an T6, plus V6 to Z6, plus AB to AK. It would be
great if you knew this one, too.

Thanks,

Michele

-----------------