Forum Discussion
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 a row with the same text "Regulated Work" - this is required to highlight if someone was tasked to work more than 6 days straight in a roster.
Thanks
Muffinman Perhaps the example in the attached workbook does what have in mind.
5 Replies
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
Muffinman Perhaps the example in the attached workbook does what have in mind.
- Riny - was playing around with this and when i apply it to multiple rows it totals all ''regulated work'' and highlights entire sheet when it finds more than 6. How can i isolate this formula to only calculate regulated work >6 by occurrences in a row only? The only way i can get it to do a row only calculation (and only highlight when it finds >6 in that row) is to apply to one row only then duplicate formula apply to the next row and then change the row number and repeat this process for all rows. I would show a screenshot to make it easier to understand but cant seem to get it approved through the editor on this site
- Riny_van_Eekelen - you are a genius!!! Works perfectly thankyou so much:)
