Excel conditional formula

Hi i have a problem understanding this I want to color highlight numbers from row 2 that is row 1 + 5 I mean if any number from row 2 + 5 equal to any number in row 1 to be formatted and fill...

excel

Formulas and Functions

Lorenzo

Silver Contributor

Oct 30, 2025

A TypeScript option for the exercise only (my 3rd script - be gentle please)
NB: Do not use it with large ranges

// TypeScript: Color cells on condition:
//  IF cell value in Range_to_color + Increment = any cell value in Range_of_references
//  THEN color cell in Range_to_color
//
//  ASSUMPTION: Range_of_references & Range_to_color consist of numbers only (empty cells OK)
function main(
  workbook: ExcelScript.Workbook,
  Range_of_references: string = "A2:F2",
  Range_to_color: string = "A3:F3",
  Increment: number = 5
) {

  function normalizeAddress(address: string): string {
    // Trim and convert to uppercase for consistent address validation
    return address.trim().toUpperCase();
  }

  function isValidAddress(address: string): boolean {
    // Basic regex to validate Excel-style range references (e.g., A1:B3, B10:C10...)
    const regExcelRange: RegExp = /^[A-Z]+[1-9][0-9]*:[A-Z]+[1-9][0-9]*$/;
    return regExcelRange.test(normalizeAddress(address));
  }

  // Validate the input address format (e.g., "A1", "B100", "AZ35")
  if (!isValidAddress(Range_of_references) || !isValidAddress(Range_to_color)) {
    // Execution stops on throw (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/throw)
    throw new Error("Please enter valid range address(es) like A1:D1, C4:G4, ...");
  }

  // Get active worksheet and ranges
  const worksheet = workbook.getActiveWorksheet();
  const sourceRange = worksheet.getRange(Range_of_references);
  const targetRange = worksheet.getRange(Range_to_color);

  // Count columns in targetRange
  const targetColumns: number = targetRange.getColumnCount();

  // Flatten sourceRange (workaround to .flat() that raises a warning in Excel TS console)
  const flattenedSource = sourceRange.getValues()
    .reduce((acc, row) => acc.concat(row), []);

  // Iterate cells in targetRange
  for (let i = 0; i <= targetColumns; i++) {
    const currentCell = targetRange.getCell(0, i);
    // Invoking read methods inside of a loop could lead to slow perf.:
    const currentValue = Number(currentCell.getValue());

    // Iterate flattenedSource
    for (let j = 0; j <= targetColumns; j++) {
      // If currentValue (in targetRange) + Increment = current flattenedSource value...
      if (flattenedSource[j] === currentValue + Increment) {
        // Color the cell in targetRange
        currentCell.getFormat().getFill().setColor("#C1F0C8");
        break
      }
    }
  }
}

SergeiBaklan

Diamond Contributor

Oct 30, 2025

Great approach. Here is with slightly modified second part

function main(
    workbook: ExcelScript.Workbook,
    Range_of_references: string = "A2:F2",
    Range_to_color: string = "A3:F3",
    Increment: number = 5
)

// see https://techcommunity.microsoft.com/discussions/excelgeneral/excel-conditional-formula/4464321

 {

    function normalizeAddress(address: string): string {
        // Trim and convert to uppercase for consistent address validation
        return address.trim().toUpperCase();
    }

    function isValidAddress(address: string): boolean {
        // Basic regex to validate Excel-style cell references (e.g., A1:B3, B10:C10...)
        const regex = /^[A-Z]+[1-9][0-9]*:[A-Z]+[1-9][0-9]*$/;
        return regex.test(normalizeAddress(address));
    }

    // Validate the input address format (e.g., "A1", "B100", "AZ35")
    if (!isValidAddress(Range_of_references) || !isValidAddress(Range_to_color)) {
        // Execution stops on throw (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/throw)
        throw new Error("Please enter valid range address(es) like A1:D1, C4:G4, ...");
    }

    // Get active worksheet and ranges
    const worksheet = workbook.getActiveWorksheet();
    const sourceRange = worksheet.getRange(Range_of_references);
    const targetRange = worksheet.getRange(Range_to_color);


    /////////////////////////////////////////////////////////////////////
    // Another variant
    const flattenedSource = sourceRange.getValues()[0]
    const incremented = targetRange.getValues()[0].map(x => +x + Increment)

    targetRange.getFormat().getFill().clear()
    Array.from( incremented.keys())
            .filter(n => incremented
                .map(x => flattenedSource.includes(x))[n])
            .map( m => targetRange.getCell(0, m).getFormat().getFill().setColor("lightgreen"))
    // end of another variant
    /////////////////////////////////////////////////////////////////////////


/*
    // Count columns in targetRange
    const targetColumns: number = targetRange.getColumnCount();

    // Flatten sourceRange (workaround to .flat() that raises a warning in Excel TS console)
    const flattenedSource = sourceRange.getValues()
        .reduce((acc, row) => acc.concat(row), []);

    // Iterate cells in targetRange
    for (let i = 0; i <= targetColumns; i++) {
        const currentCell = targetRange.getCell(0, i);
        // Invoking read methods inside of a loop could lead to slow perf.:
        const currentValue = Number(currentCell.getValue());

        // Iterate flattenedSource
        for (let j = 0; j <= targetColumns; j++) {
            // If currentValue (in targetRange) + Increment = current flattenedSource value...
            if (flattenedSource[j] === currentValue + Increment) {
                // Color the cell in targetRange
                currentCell.getFormat().getFill().setColor("lightgreen")  //   ("#C1F0C8");
                break
            }
        }
    }
*/

}

Forum Discussion

Excel conditional formula

Resources