I'm trying to set Conditional Formatting for an entire column except the first row ("F2:F65535") with a formula that I want to active relatively. For example if my formula is
=SOMEFUNCTION(B2,C2)
in F2, I want it to be
=SOMEFUNCTION(B3,C3)
in F3.
Aspose and Spreadsheet gear appear to support this functionality... for example here is the same thing in ASPOSE.Cells..it knows to adjut the formula in formula1 appropriately.
dvRange = new CellArea();
dvRange.StartRow = 1;
dvRange.EndRow = 65535;
dvRange.StartColumn = 5;
dvRange.EndColumn = 5;
dvCurrentIndex = _this.Worksheets["DataEntry"].Validations.Add();
v = _this.Worksheets["DataEntry"].Validations[dvCurrentIndex];
v.AlertStyle = ValidationAlertType.Information;
v.Type = ValidationType.List;
v.AreaList.Add(dvRange);
v.Formula1 = "=OFFSET(StateListDropdown,MATCH(SUBSTITUTE(UPPER(A2),\" \",\"\"),StateValidator,0)-1,0,COUNTIF(StateValidator, SUBSTITUTE(UPPER(A2),\" \",\"\")),1)";
v.InCellDropDown = true;
v.IgnoreBlank = false;
v.ShowInput = false;
v.InputTitle = "Mock Entry";
v.InputMessage = "Enter or choose a color";
v.ShowError = false;
v.Operator = OperatorType.None;
demo_450de443.xls
