Activecell.offset

[Fazila]

Hi

I need to use the code below to attach a data validation list to columns K, L and M. The only thing is the row for each sheet is different hence the use of Activecell. The code I have is:

Sub IntList()'
' IntList Macro
'
   
    Range(ActiveCell.Offset(1, 0), ActiveCell.End(xlDown)).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Lookups!$B$2:$B$8"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Range(ActiveCell.Offset(0, 1), ActiveCell.End(xlDown)).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Lookups!$A$2:$A$8"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Range(ActiveCell.Offset(0, 2), ActiveCell.End(xlDown)).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Lookups!$C$2:$C$8"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

The problem is once it has finished with the first selection it does not move onto the next column but highlights both columns K and L. I have different lists for each column. How can I change the code so that after the first data validation has applied it moves on to column L and then to column M?

Thanks

Fazila

 

[steve the fish]

If you only want it to work on column K,L,M then its best to use K,L,M in the code not activecell.

 

[Fazila]

Thanks Steve

The problem is I need to make my range so that it starts from the row below the active cell and column k, then l and then m.

At the moment my range is:

K66:k90

then L66:l90

then M66: m90

The problem is for the next sheet the row number could be 70 to 200 or 76 to 150 so it means I have to go into the code each time to alter the row references.

 

[steve the fish]

Ok somthing like this?

Dim rng As Range, myRow As Long

myRow = ActiveCell.Row
Set rng = Range("K" & myRow)

Range(rng.Offset(1, 0), rng.Offset(1, 0).End(xlDown)).Select
'code
Range(rng.Offset(1, 1), rng.Offset(1, 1).End(xlDown)).Select
'code
Range(rng.Offset(1, 2), rng.Offset(1, 2).End(xlDown)).Select
'code

 

[Fazila]

Thank you Steve!!