Forum Discussion
Solved
Conditional Formatting Formula Needed
Hi members - I was wondering if someone could help me with what should be an easy formula. I need a formula in conditional formatting to highlight in red if there is more than 6 consecutive cells in ...
Muffinman Perhaps the example in the attached workbook does what have in mind.
This is just playing.
WorksheetFormula = LET(
consecDays, SCAN(0, roster="Regulated Work", Consecλ),
excessDays, consecDays>6,
FILTER(day, excessDays));
Consecλ
= LAMBDA(a, b, IF(b, a+1, 0));returns a list of excess days scheduled. The array excessDays could be used for conditional formatting if the CF functionality were not so antiquated (I suspect the code is out of living memory). If you simply wanted to conditionally format the name or the entire row, the following formula could also be used
= OR( SCAN(0, roster="Regulated Work", Consecλ) > 6 )I realise that this probably looks totally over the top but, for me, almost every formula in the workbook is a Lambda function so anything else looks kind of out of place.
- Thanks for the response Peter - wasn't able to apply it to my sheet but appreciate your help
