Conditional formatting based on range of values in different column?
I've tried several different ways to do this but clearly I'm missing something.
I have spreadsheet that shows the hours scheduled on all the machines at our facility. The dates in row 2 auto populate based on today's date. I want to make a rule that changes the formatting of rows 3 - 12 if any of the dates in row 2 are company holiday. For example, if B2 matches any of the dates in R3 through R12, I want B3 through B12 to fill in black with red text, so that way it shows that date is blacked out because we're closed, but if someone accidentally scheduled an order that day the machine hours will show in red so they know to reschedule it.
On the original version of this spreadsheet I made a few years ago I had the same issue and end up just using =B$2=$R$3, so for each day I had to make 10 different rules for every holiday in column R. I knew that definitely was not the best way to do it but I was tired of messing with it and just needed it to work. I am updating this spreadsheet and it now shows the next 60 days instead of the next 14 days and I know there's got to be a better solution than creating 10 different rules for each day. I know =B$2=$R$3:$R$12 does NOT work and I think I'm starting to smell burnt toast.
[link] [comments]
Want to read more?
Check out the full article on the original site