Home |
Search |
Today's Posts |
#1
|
|||
|
|||
ranges and columns
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 |
#2
|
|||
|
|||
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" wrote in message om... 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 |
#3
|
|||
|
|||
Thanks
works a treat "Otto Moehrbach" wrote in message ... 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" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why can't a copy and paste columns? | Excel Worksheet Functions | |||
Conditional Formula to search ranges?? | Excel Discussion (Misc queries) | |||
Problem with graph ranges | Charts and Charting in Excel | |||
Ranges with in a LOOKUP | Excel Worksheet Functions | |||
SUMIF and 3-D Ranges | Excel Worksheet Functions |