Loop help needed to repeat macro so that it works for every completed cell in a column

Hi Guys

I have managed to create a macro that links to a data validation drop down list, however I can only get it to work by looking at one cell at a time. As the column will be using the data validation in every row, I would ideally like it to auto repeat until a blank cell is found.

Here is what I have come up with so far:-

ub CRG()

ActiveCell.Offset(0, 1).Select


End Sub

Sub DRSS_Service()

ActiveCell.Offset(0, 4).Select

End Sub


Sub Member_of_Staff()

ActiveCell.Offset(0, 13).Select

End Sub


Sub T_and_D()

ActiveCell.Offset(0, 16).Select

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Q3")) Is Nothing Then
Select Case Range("Q3")
Case "A CRG": CRG
Case "The DRSS Service": DRSS_Service
Case "A Member of Staff": Member_of_Staff
Case "Training or Development": T_and_D
End Select
End If

Any help in getting the 'Private Sub Worksheet_Change(ByVal Target As Range)' to work on each and every cell in column Q would be greatly appreciated.

Try changing Range("Q3") to Range("Q:Q")

Can you attach a copy of your file. De-sensitize the data if necessary. Explain in detail what you want to do referring to specific cells, rows and columns.

Try:

Please Login or Register  to view this content.

You are very welcome.