vba and cell validation

[james_lankford]

let's say column A contains a date an error happened in some process (this is pulled from a database, I have no idea how many rows there will be or what the dates will be)

in column B the user will type in the corrective action taken

in column C the user will enter the date the corrective action was taken

I want to make sure the user enters a date >= to the date of the error (so column C must be >= column A)

I can't figure out what the Formula1 parameter of the Add method should be
for row 2 I want it to be A2
for row 3 I want it to be A3
and so on

I've tried offset of the active cell but that didn't work; I really have no idea what I'm doing

' row 1 contains the header, so I start on row 2

With .Range(.Cells(2, 3), .Cells(.Rows.Count, 3)).Validation
     .Delete
     .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:=xlGreaterEqual, Formula1:="=" & ??????
     .IgnoreBlank = True
     .InCellDropdown = True
     .InputTitle = "Date of corrective action"
     .InputMessage = "Please enter the date of the corrective action."
     .ErrorTitle = "I'm sorry"
     .ErrorMessage = "There was an error. Please hit Cancel and try again."
     .ShowInput = True
     .ShowError = True
End With

ok, forget it
I put Formula1:="=A2"
and it seems to "fill down" correctly

 

[Jonmo1]

I got this from the Macro Recorder...

.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
xlGreaterEqual, Formula1:="3/15/2009"

You can just replace "3/15/2009" with Range("A2") or whatever...

Should be

.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
xlGreaterEqual, Formula1:="=A2"

 

[james_lankford]

Should be

.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
xlGreaterEqual, Formula1:="=A2"

yeah, its really great how excel works
I have hundreds of rows that need validation and yet simply by typing "=A2" its smart enough to update the reference for each of those rows

I spent 15 minutes playing around with offsets and stuff, totally unnecessary, I was making it a lot more difficult that it needed to be

 

[Jonmo1]

Yep, as long as you're entering the formula (validation) into ALL cells in the range at once, that works. You can also lock it to a single cell like =$A$1, that way all cells refer to A1, instead of incrementing (sometimes that's what you want, sometimes not).

It's the same when entering formulas manually...

If you highlight a whole range of cells, say A1:E10, and type =F1 and press CTRL + ENTER, then it will do the same. A1 refers to F1, A2 = F2, B1 = G1 etc...

That is a really cool functionality.