Excel 2007 : conditional formatting???

[mulvoy]

i have 32 rows each with 16 columns of data. i want to use the data in one row to say highlight anything on this spreadsheet that matches any of the datapoints in this one row. I don't need to use more than one color. i just want to easily see all other places where the data matches one of the cels in my row. i assume there's a way to do this. Can anybody help??

[NBVC]

Assuming your data is in A1:P32 and A1:P1 is the row you want to compare to.. select A2:P32 and go to Home|Conditioal Formatting and select New Rule. Select use a formula to determine which cells to format... and then enter formula:

=COUNTIF($A$1:$P$1,A2)

click Format and choose colour from Fill tab.

Click Ok

Click Ok again to finish.

[mulvoy]

Thanks, but either that doesn't work or I just have no idea what I'm doing. i'm attaching the file here. what i'm trying to do is highlight any of the cels that equal the values in row 4, columns C, F, I, L and so forth. This is for a football office pool and i want to see the picks that are equal to mine. thank you.

[NBVC]

So assuming you are interested in column by column comparisons.. try selecting C2:AP33 and applying conditional format formula:

=AND(MOD(COLUMN()+1,3)=1,ROW()<>4,C2=C$4)

See attached.

[mulvoy]

This seems to be exactly what i need. Thanks! So just to clarify, in case I need to add columns or rows or change the format in any way...what does that formula say exactly? Does it say "take every 3rd column (from the starting point column A) and compare it to its row 4 counterpart?" Or is there more to it?
Thanks again. This is great.

[NBVC]

That's pretty much it.

[mulvoy]

ok. last time i bug you, i promise. attached is a slightly different version of the same spreadsheet. this time there is an extra column at the beginning and the end and i have used the freeze panes functionality also. the data i want to compare is in columns D, G, J, etc... i tried using the formula you gave me earlier and i made changes that i thought would work, but i can't get it to highlight the data i want. could you help once more? thanks!

[NBVC]

The formula changes slightly to accomodate the offset of one more column...

=AND(MOD(COLUMN(),3)=1,ROW()<>4,C2=C$4)

or

=AND(MOD(COLUMN()+1,3)=2,ROW()<>4,C2=C$4)

[mulvoy]

this is the best i could get it. (attached) It's highlighting in the correct columns but it doesn't seem to care whether it matches whats in row 4 or not. several conflicting values are highlighted. if you see what's wrong here can you tell me? and do you mind stating exactly what the logic says so I know how to adjust better going forward? thank you!

[NBVC]

Adjust the formula to:

=AND(MOD(COLUMN(),3)=1,ROW()<>4,D2=D$4)

and make sure the Applies To field shows =$D$2:$AN$33 so that the range is aligned to the conditional formatting.

[mulvoy]

perfect! it worked. Thanks!