Excel OfficeScript with multiple conditional formatting rules AND Loop through Columns - Stack Overflow

admin管理员组

文章数量:1305571

I used this post as a template for creating multiple conditional formats, however, I am also trying to include a loop function for Range H5:XX5 to start at H5, check the cell if it matches a month (as a number 1-12), highlight cell accordingly, then move five columns to the right through Cell XX5. Once we figure this out, I can likely figure out how to complete a second set of Cond Formatting on Row 6, below my dates, using the helper cells highlighted in yellow. See Image Here

Template code: Excel OfficeScript with multiple conditional formatting rules

My code below is not quite working yet, likely due to the Month formula. I was able to successfully get it working following the 1st Answer, but not the 2nd Answer, and also not with a loop. Looking for assistance to add in a loop, as described above.

function main(workbook: ExcelScript.Workbook) {
  // Get the range to format.
  const shAttendance = workbook.getWorksheet("Drew's Workload");
  const rngAttendanceData = shAttendance.getRange("H5:XX5");
  
let condFormat: object[] = [
    ["=Month(H5)=1", "Blue"],
    ["=Month(H5)=2", "Yellow"],
    ["=Month(H5)=3", "#9C5701"],
    ["=Month(H5)=4", "#9C5702"],
    ["=Month(H5)=5", "#9C5703"],
    ["=Month(H5)=6", "#9C5704"],
    ["=Month(H5)=7", "#9C5705"],
  ]
  for (let cFormat of condFormat) {
    let conditionalFormat = rngAttendanceData.addConditionalFormat(
      ExcelScript.ConditionalFormatType.cellValue).getCellValue();
    let rule: ExcelScript.ConditionalCellValueRule = {
      formula1: cFormat[0],
      operator: ExcelScript.ConditionalCellValueOperator.equalTo
    };
    conditionalFormat.setRule(rule);
    let format = conditionalFormat.getFormat();
    format.getFill().setColor(cFormat[1]);
  }
}

本文标签： Excel OfficeScript with multiple conditional formatting rules AND Loop through ColumnsStack Overflow