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
_________________
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top