Can't figure out how to set Validation to formula

  • Hi all,
    First time poster, long time lurker! Really struggling with this and can't seem to find an answer anywhere. Hoping someone here has a solution for me.

    I'm trying to set Data Validation for a cell via vba. The cell needs to exist on each row of data the rest of my code generates, so I can't hardcode the cell referencing. Also, this needs to be a dynamic validation, so I'm trying to use an indirect formula to drive things. Here's the sub I'm using:

    When I try to run it, I get a run-time 1004 error on the line adding Formula1 etc.

    Is it possible to do what I'm trying to do, and if so, how do I do it?

    Thanks in advance!

  • Hi all,
    Since as far as I can tell what I was trying to do is just not possible, I took a different route to solve my problem.

    For any folks that are curious, I instead built out a sub that reacts to any change in the range of cells that constitute the first validated entry (ie the driver for this validation) and then adds the appropriate validation based on that through simple if statements.