Changing cell color

[Guest]

I have bunch of survey questions that ask for answers that rank importance.
Each question is followed by 5 cells in a row and I want to change the color
of one of these cells when someone selects it. Changing the selected cell in
a row would then toggle the old one and change the color of the new one
picked.

 

[Guest]

Lets say your questions are in column A and the answers are in cols B thru F.
Put the following macro in the worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long
If Intersect(Target, Range("B:F")) Is Nothing Then Exit Sub
r = Target.Row
rs = "B" & r & ":F" & r
Range(rs).ClearFormats
Target.Interior.ColorIndex = 6
End Sub

 

[Guest]

Well, not only did this work, but you even guessed the correct format of my
sheet columns A-F. Thanks very much and that was very interesting.
Alf