Auto Colour based on answer key

[bushidowarrior]

Hello All,

I have this table below. As you can see in the KEY column, is has the correct answer. In this case it is C.

Is there a was to make "WHAT" the correct answer highlight in GREEN?

And for the next row it would be D (They're)

I would really appreciate it.

I have over 100 questions, hence I would like to automate this process a little.

Thank you.

 

[bushidowarrior]

Please have a look just in case I missed something....

 

[Akuini]

Sorry, I can't figured out why.
Try copying the data as value only to a new workbook & see what happen.

 

[bushidowarrior]

Same error... Strange. I am looking into it.

 

[bushidowarrior]

WORKING... Thank you.. I have no idea what happened. New sheet and all is working. THANK YOU VERY MUCH

 

[bushidowarrior]

Found the bug. A space after one of the KEY answers.

 

[Akuini]

Found the bug. A space after one of the KEY answers.

Ok, glad you figured it out.

 

[bushidowarrior]

Hello,


Sorry I forgot to ask one thing.. My sincerest apologies but I think it's quite quick.


Whatever the correct answer is, which has been highlighted in green. Can I then copy that information to: J.

I am sorry I did not mention it before, it had slipped my mind...

THANK YOU VERY MUCH!!!

 

[Akuini]

Hello,


Sorry I forgot to ask one thing.. My sincerest apologies but I think it's quite quick.


Whatever the correct answer is, which has been highlighted in green. Can I then copy that information to: J.

I am sorry I did not mention it before, it had slipped my mind...

THANK YOU VERY MUCH!!!

OK, try this:
And this revised code also should take care the trailing space problem so you don't have to worry about that.

Sub a1075737b()
'https://www.mrexcel.com/forum/excel-questions/1075737-auto-colour-based-answer-key.html
Dim r As Range, s As String
For Each r In Range("E2", Cells(Rows.count, "E").End(xlUp))
s = Trim(r)
If s Like "[A-D]" Then
Range(s & r.row).Offset(0, 5).Interior.Color = vbGreen
Range("J" & r.row) = Range(s & r.row).Offset(0, 5)
End If
Next

 

[bushidowarrior]

Absolutely fantastic.. Works perfectly and thank you for taking care of the training spaces..


I owe you a few beers... Whatever you want

 

[Akuini]

Absolutely fantastic.. Works perfectly and thank you for taking care of the training spaces..


I owe you a few beers... Whatever you want

Hahaha, thank you, but beer is hard to sent... but bitcoin isn't... ...kidding.