Modify Validation

You can change the validation rule for a range by using either of the two ways described below:

Set properties of the IValidation interface (Type property, Formula1 property, Formula2 property, and many more).
Use Delete method of the IValidation interface to first delete validation rule and then use the Add method to add the new rule.

Refer to the following example code to know how you can modify an existing validation rule applied to a cell or a range of cells in a worksheet.

C#	Copy Code
//Add validation worksheet.Range["A1:A2"].Validation.Add(ValidationType.Date, ValidationAlertStyle.Stop, ValidationOperator.Between, new TimeSpan(13, 30, 0), new TimeSpan(18, 30, 0)); //Modify validation. worksheet.Range["A1:A2"].Validation.Type = ValidationType.Time; worksheet.Range["A1:A2"].Validation.AlertStyle = ValidationAlertStyle.Stop; worksheet.Range["A1:A2"].Validation.Operator = ValidationOperator.Between; worksheet.Range["A1:A2"].Validation.Formula1 = new TimeSpan(13, 30, 0).TotalDays.ToString(); worksheet.Range["A1:A2"].Validation.Formula2 = new TimeSpan(18, 30, 0).TotalDays.ToString();

Copy Code

//Add validation
worksheet.Range["A1:A2"].Validation.Add(ValidationType.Date, ValidationAlertStyle.Stop, ValidationOperator.Between, new TimeSpan(13, 30, 0), new TimeSpan(18, 30, 0));

//Modify validation.
worksheet.Range["A1:A2"].Validation.Type = ValidationType.Time;
worksheet.Range["A1:A2"].Validation.AlertStyle = ValidationAlertStyle.Stop;
worksheet.Range["A1:A2"].Validation.Operator = ValidationOperator.Between;
worksheet.Range["A1:A2"].Validation.Formula1 = new TimeSpan(13, 30, 0).TotalDays.ToString();
worksheet.Range["A1:A2"].Validation.Formula2 = new TimeSpan(18, 30, 0).TotalDays.ToString();