Change fill color of cell

[BillP]

Hello,
Excel 2k on Vista 64 bit.
I have a range of cells (b2:bn2) in a row that are answers to a quiz. In the
same cells below are the answers with a different quiz participant in each
row. I would like to change the background of the cells that do not match
the answers in b2:bn2. I have used conditional formatting and accomplished
this task, however, I would also like to total the wrong answers in column
"bo" for every row. I have found a bit of programming on line but it doesn't
work with conditional formatting. It does work when I format the cells by
hand. Any suggestions?
Thanks for your input.
Bill

 

[BillP]

The contents of cells b2:bn2 are text, letters A thru E, corresponding to
the correct letter in multiple choice answers.
Sorry for not including in original post.
Bill

 

[Rick Rothstein]

Put this formula in BO3 and copy it down as needed...

=SUMPRODUCT((B3:BN3<>"")*($B$2:$BN$2=B3:BN3))

Note: This formula will work independently from your Conditional Formatting
(that is, it will work whether you are using Conditional Formatting or not).

 

[BillP]

Thank you Rick,
Worked perfect!
Bill

Put this formula in BO3 and copy it down as needed...

=SUMPRODUCT((B3:BN3<>"")*($B$2:$BN$2=B3:BN3))

Note: This formula will work independently from your Conditional
Formatting (that is, it will work whether you are using Conditional
Formatting or not).