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]

I change it to A2, and A

 

[Akuini]

If you've tried the last code (post #10 ) & still didn,t work then maybe there are some typos in your data like a trailing space.
If that is the case then I could modify the macro to handle the typos.

 

[bushidowarrior]

If you've tried the last code (post #10 ) & still didn,t work then maybe there are some typos in your data like a trailing space.
If that is the case then I could modify the macro to handle the typos.

okay. I am not sure what is going on. But I am using your first code just to get it working.

 

[Akuini]

Try this, KEY in col E not A:

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

 

[bushidowarrior]

Try this, KEY in col E not A:

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

I gave it a god an nothing happened.

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

 

[bushidowarrior]

 

[Akuini]

The data should be in col E to I, like your image in post #3 .

Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#008B8B][th] [/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

1​

[/td][td]key[/td][td]A[/td][td]B[/td][td]C[/td][td]D[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

2​

[/td][td]C[/td][td][/td][td][/td][td=bgcolor:#00FF00][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

3​

[/td][td]D[/td][td][/td][td][/td][td][/td][td=bgcolor:#00FF00][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

4​

[/td][td]A[/td][td=bgcolor:#00FF00][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]

 

[bushidowarrior]

Yes, you are right.. Perfect, Thank you...

When I try to do it on my normal sheet. I get this message. I am not sure why it works on one sheet but not the other. Everything is identical between both sheets.

 

[bushidowarrior]

I am using office 2016. I am not sure if that makes a difference.

 

[Akuini]

Hm, not sure why that happened.
You put the code in normal module not in sheet module, right?