Highlight wrong answer

thetank

Board Regular
Joined
Mar 17, 2005
Messages
142
I am trying to keep track of the college bowl games and would like some help from excel.

I have about 30 people in the "pool". I have all the names in column A and each of their corresponding picks out to the right.

I would like to be able to type in the winner of each game at the top of the column and have excel highlight the winners.

I would also like to have excel put the names in order of most right to most wrong. I am sure this will have to be done with a macro, but I don't know the code well enough to figure it out.

I hope you can help. Thanks in advance.

Below is a small example:
If this were in excel, I would like the CSU in craigs row to be highlighted red and also the UT and CSU in Johns row.

SM Tol Cal Na
Brian C SM Tol Cal Na
Craig S SM Tol Cal CSU
John H UT Tol Cal CSU
_________________
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Let us see if I have understood you correctly ...
Book2
ABCDEF
1SMTolCalNaNoOfRights
2BrianCSMTolCalNa4
3CraigSSMTolCalCSU3
4JohnHUTTolCalCSU2
5
Sheet3


For highliting the winning entries, I selected the cells B2:E4, and with B2 as the ActiveCell, I used the following Conditional Formatting formula ...

=B2-B$1 ... pattern: color Yellow

and for the count of the right entries in cells F2:F4, I used the following array formula in cell F2 ...

=SUM(IF(B2:E2=$B$1:$E$1,1))

this is then copied down.

Is this what you are looking for?
 

thetank

Board Regular
Joined
Mar 17, 2005
Messages
142
Yes, but I would also like to sort them from most right to most wrong. That way the person who hasn't gotten a wrong answer will be at the top and the person who missed the most will be at the bottom. Also, while we are at it, how about sorting them in alphabetical order within the most right/wrong order.

Will that require a macro?

Thanks for your help.
 

thetank

Board Regular
Joined
Mar 17, 2005
Messages
142
How do you do an array formula? I couldn't figure it out.

I am only highlighting the wrong answers in red. But for the games that haven't been played yet, the cells are returning the answers in red. How can I make it only highlight the cells where there is an answer? ie, if no answer is input, all of the cells remain un-highlighted.

Under conditional formating, I entered the formula:
=B2<>B$1

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,119,139
Messages
5,576,308
Members
412,716
Latest member
thviid
Top