Selected dropdown value pushes to other cells

[Matthew Johnson]

Am currently working up a workflow checklist with various drop down cells for
each task. One column the worksheet is a risk level with Data Validated
options of High, Medium, Low or N/A. Other columns indicate item status,
action by, etc.

What I want to happen is that when someone selects N/A in the risk column
(eg E24) the other columns (eg G24, H24 and I24) default to N/A as well
otherwise they allow selction from other dropdown lists. Don't have much
experience with VB but willing to give it a go if needed.

 

[Roger Govier]

Hi Matthew

The following should get you started.
I have assumed you don't want it to apply to rows before 24, but to any row
from 24 onward

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tr As Long, tc As Long
tr = Target.Row
tc = Target.Column

If Target.Count > 1 Then Exit Sub
If tr < 24 Then Exit Sub
If tc <> 5 Then Exit Sub

If UCase(Cells(tr, 5)) = "N/A" Then
Application.EnableEvents = False
Range(Cells(tr, 7), Cells(tr, 9)) = "N/A"
Application.EnableEvents = True
End If

End Sub

To use
Copy code
Right click on sheet where you want the code>View code
Paste the code into the white pane that appears
Alt + F11 to return to Excel

--
Regards
Roger Govier

Am currently working up a workflow checklist with various drop down cells
for
each task. One column the worksheet is a risk level with Data Validated
options of High, Medium, Low or N/A. Other columns indicate item status,
action by, etc.

What I want to happen is that when someone selects N/A in the risk column
(eg E24) the other columns (eg G24, H24 and I24) default to N/A as well
otherwise they allow selction from other dropdown lists. Don't have much
experience with VB but willing to give it a go if needed.

__________ Information from ESET Smart Security, version of virus
signature database 4805 (20100125) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4805 (20100125) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4810 (20100127) __________

The message was checked by ESET Smart Security.

http://www.eset.com