Scripting and Macros

Highlight Blank Cells

function main(workbook: ExcelScript.Workbook) { 
  const selectedRange = workbook.getSelectedRange(); 

  const conditionalFormatting = selectedRange.addConditionalFormat( 
    ExcelScript.ConditionalFormatType.presetCriteria 
  ); 
  conditionalFormatting.getPreset().setRule({ 
    criterion: ExcelScript.ConditionalFormatPresetCriterion.blanks, 
  }); 

  const conditionalFormat = conditionalFormatting.getPreset().getFormat(); 
  conditionalFormat.getFill().setColor("yellow"); 
  const borderStyle = ExcelScript.ConditionalRangeBorderLineStyle.continuous; 
  conditionalFormat.getConditionalRangeBorderTop().setStyle(borderStyle); 
  conditionalFormat.getConditionalRangeBorderBottom().setStyle(borderStyle); 
  conditionalFormat.getConditionalRangeBorderLeft().setStyle(borderStyle); 
  conditionalFormat.getConditionalRangeBorderRight().setStyle(borderStyle); 
  conditionalFormatting.setStopIfTrue(false); 
  conditionalFormatting.setPriority(0); 
} 

This script applies green fill conditional formatting to the top 10% of values in the used range.

function main(workbook: ExcelScript.Workbook) { 
  let selectedSheet = workbook.getActiveWorksheet(); 
  let range = selectedSheet.getUsedRange(); 

  let conditionalFormat = range.addConditionalFormat(ExcelScript.ConditionalFormatType.topBottom) 
  conditionalFormat.getTopBottom().getFormat().getFill().setColor("green"); 
  conditionalFormat.getTopBottom().setRule({ 
    rank: 10, 
    type: ExcelScript.ConditionalTopBottomCriterionType.topPercent 
  }); 
}