ranges and columns

[Kevin]

hi
i have a worksheet that contains list of questions in rows
i then have three columns called OK, Not OK and N/A
the user must select one of these cells(in line with the question)
Whati want to do is
if the user selects Not ok and realises he/she has made a mistake, if
the user then selects ok or N/A it clears the contents of Not ok Cell
and put X in relevent cell.

can anyone help with this

thanks

kevin

 

[Otto Moehrbach]

Kevin
The following two sheet macros do what you want. Right click the sheet
tab, select View code, and paste these macros into the displayed module.
HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
If Target.Column < 4 Or Target.Column > 6 Then Exit Sub
If Target.Column = 4 Then
Application.EnableEvents = False
Target.Offset(, 1).Resize(, 2).ClearContents
Application.EnableEvents = True
End If
If Target.Column = 6 Then
Application.EnableEvents = False
Target.Offset(, -2).Resize(, 2).ClearContents
Application.EnableEvents = True
End If
If Target.Column = 5 Then
Application.EnableEvents = False
Target.Offset(, -1).ClearContents
Target.Offset(, 1).ClearContents
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 4 Or Target.Column > 6 Then Exit Sub
Target = "X"
End Sub

 

[Kevin]

Thanks
works a treat

Kevin
The following two sheet macros do what you want. Right click the sheet
tab, select View code, and paste these macros into the displayed module.
HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
If Target.Column < 4 Or Target.Column > 6 Then Exit Sub
If Target.Column = 4 Then
Application.EnableEvents = False
Target.Offset(, 1).Resize(, 2).ClearContents
Application.EnableEvents = True
End If
If Target.Column = 6 Then
Application.EnableEvents = False
Target.Offset(, -2).Resize(, 2).ClearContents
Application.EnableEvents = True
End If
If Target.Column = 5 Then
Application.EnableEvents = False
Target.Offset(, -1).ClearContents
Target.Offset(, 1).ClearContents
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 4 Or Target.Column > 6 Then Exit Sub
Target = "X"
End Sub

 

[Kevin]

I have a query regarding the code
If i select a merged cell the sheet code crashes
the only way i can make the code active again is to close the workbook
and reopen it.
Can this code be adapted to deal with merged cells and is it posible
to restrict the number of rows

thanks
kevin