Forum Discussion
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...
Another point it's hard to test with regex all valid addresses, from A1 to XFD1048576. Expression will be too complex. However, OfficeScript checks itself if we use correct range, thus we may rely on it and use try/catch. In addition it's only to add our own rule which doesn't allow to run script with single cell ranges. Something like
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
{
const worksheet = workbook.getActiveWorksheet()
if (!Range_of_references.includes(":") || !Range_to_color.includes(":")) {
throw new Error(`Keep ":" in range addresses`)
}
try {
worksheet.getRange(Range_of_references)
worksheet.getRange(Range_to_color)
} catch (error) {
console.log(`Incorrect (${Range_of_references}) or (${Range_to_color}) address. Please inspect and run again.`)
return
}
const sourceRange = worksheet.getRange(Range_of_references)
const targetRange = worksheet.getRange(Range_to_color)
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"))
}
Agreed, regex isn't the appropriate approach (did it for the exercise) & your try...catch approach should do a better jon
Good training material for me :) who started with JS/TS a few days ago. Very concise approach:
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"))Another comment, with
const flattenedSource = sourceRange.getValues()[0]I assumed we work with rows, otherwise above doesn't work. In general , flat() works even if the editor reports the problem. Both of these
const v = workbook.getActiveWorksheet().getUsedRange().getValues() const flattenedA: (number | string | boolean) [] = v.flat() const flattenedB: (number | string | boolean) [] = [] for (let row of v) { for (let cell of row) { flattenedB.push(cell) } }return exactly the same array.