Dear users,

I have an excel sheet that activates a macro to create a data validation list when a certain area is activated by double clicking.
This datavalidation list is based on the value to cell to the left of where the validation list will appear.
The formula looks like this:
=OFFSET(Customerstart;MATCH(G7 ;Customercolumn;0)-1;1;COUNTIF(Customercolumn; & G7);1)
Because the cell G7 wil change for each data validation to the row where the validation list is created i tried to adapt the code to what you see below. But unfortunately it does not work and gives me an error on the Offset formula line (object defined error). I'm quite new to this and can't figure out how to solve this.
I hope some of you have an answer to this.

CustomerName = ActiveCell.Offset(0, -2).Value
With Selection.Validation
       .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=OFFSET(Customerstart;MATCH( & CustomerName & ;Customercolumn;0)-1;1;COUNTIF(Customercolumn; & CustomerName & );1)"
        .IgnoreBlank = True