Data Validation in VBA use RC notation

[brenner]

I have two procedures. First procedure (cell validation) has all the details that are passed to the second procedure (cell validation rules), which applies the data validation and conditional formatting. I'm trying to pass several and formulas (BtnNumberRange) using RC notation. I can only seem to pass them as strings. I need them to convert to relative references within the data validation across the validation range. My UsedArray are column numbers. My TopNumberRange are dates.


Thanks in advance.



Private Sub CellValidation()
For Each sht In ActiveWorkbook.Worksheets
LastRow = sht.Cells(LastRowExcel, 1).End(xlUp).row
TabName = sht.Name
sht.Activate

UsedArray = Array(iWsC_DTOCS, iWsC_DTOCE, iWsC_RFTOCS, iWsC_RFTOCE)
TopNumberRange = Array(Range("YearStart"), Range("YearEnd"))
BtnNumberRange = Array("=and(RC>=" & TopNumberRange(1) & "," & TopNumberRange(2) & ">RC)", "and(RC>RC[-1]," & TopNumberRange(2) & ">=RC)", "and(RC>RC[-4]," & TopNumberRange(2) & ">RC)", "and(RC>RC[-1]," & TopNumberRange(2) & ">=RC)")

NumberMessagePrompt = "Please enter an appropriate date based on the dates that you previously provided."
Call CellValidationRules(TabName, UsedArray, , , BtnNumberRange, TopNumberRange, NumberMessagePrompt)
Next sht
End Sub




Private Sub CellValidationRules(TabName, UsedArray, Optional DropDownArray, Optional CondFormExpression, Optional BtnNumberRange, Optional TopNumberRange, Optional NumberMessagePrompt)
For i = 1 To UBound(UsedArray)
ValidationRange = Sheets(TabName).Range(Cells(iWsStartRow, UsedArray(i)), Cells(LastRow, UsedArray(i))).Address
With Range(ValidationRange).Validation
.delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=BtnNumberRange(i)
.IgnoreBlank = False
.ErrorMessage = NumberMessagePrompt
End With
Next i
End Sub