Hello guys,

can you give me a hint how to do this? I want to define a macro that creates data validation for cell U2 like this (locale is German):


    With Selection.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=SUM(LEFT(U2,2),1)=19"
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = "Please write here the year!"
        .ErrorTitle = "WRONG INPUT"
        .InputMessage = "Please insert the year."
        .ErrorMessage = "Please use the following format: XX/YY""
        .ShowInput = True
        .ShowError = True
    End With
It throws an error because it cannot deal with 

"=SUM(LEFT(U2,2),1)=19"
.

What I am actually trying to do is the following. Cell U2 contains a fiscal year of the format YY/YY, like 18/19, or 19/20, etc. I want to ensure that the first number is 1 smaller than the second, so I tried this


"=VALUE(LEFT(U2;2))=VALUE(RIGHT(U2;2))-1"
"=SUM(LEFT(U2,2),1)=RIGHT(U2,2)"
"=SUM(VALUE(LEFT(U2,2)),1)=VALUE(RIGHT(U2,2))"
Whats the mistake I am doing?


Many thanks for any suggestions.

Borkar