Color change on Click using VBA

jmize9

New Member
Joined
Jul 29, 2015
Messages
2
I'm attempting to create a worksheet that could act as a test prep. I have the questions in column B in cells 2 to 400 with corresponding 4 multiple choice answers in C2 to F400 and the and the answers in the corresponding rows in column H. My goal would be to have people able to click on the answer and turn green if the value matches that in the answer column. Red if wrong.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
you might be better off placing your correct answer in column another column like G,H,I or farther to the right, using only A,B,C,D not the actual answer text. then hiding the column and the allowing the end user to type their answer in another column say G,H or I different from your hidden column and then use conditional formatting to compare their answer to the correct answer. A=A green or A=B red.
 
Upvote 0
I would use the double click rather than single click

You have a question in column B. One answer in C, another in D, E and F. The correct answer is in H.
Put this in the sheet's code module
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cells.Interior.ColorIndex = xlNone
    With Target
        If Not Application.Intersect(.Cells, Range("C2:F400")) Is Nothing Then
            Cancel = True
            If .Value <> vbNullString Then
                If .Value = .EntireRow.Range("H1").Value Then
                    .Interior.Color = RGB(0, 255, 0)
                Else
                    .Interior.Color = RGB(255, 0, 0)
                End If
            End If
        End If
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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