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...
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.