Copy/Extend/Duplicate Conditional Formatting

Occasional Contributor

Hi, I'm doing a Schedule file and I want to apply a certain color to a certain cell when there's a specific value.


Ex. If you select to take your break at 08:00 that cell on the schedule will be filled with a color.


Formula:="$BQ$4="08:00" / Format / Applies to=$F$4 

This has to be repeated for 12 different people, 3 times


I can't find a way to make this process simple rather than do it manually for each timeframe and person


Any ideas? Thanks!overview.png

9 Replies
best response confirmed by Tomas_Calvo (Occasional Contributor)


Try using =$BQ4=F$3 and apply to =$F4:$BP4

Then use the Format Painter to copy the formatting to F6:BP6 etc.

Thanks! That's exactly what I was looking for and it works perfectly.

I forgot to mention the last column, which is lunch and instead of 15 mins it's 30 mins. Can you help me with that as well?


Use the formula


Thanks again, I had to use =$BT4=OFFSET(F$3;0;-ISODD(COLUMN()))
Excel was giving me an error for using , instead of ;
This is working as well but only marks period like 08:00 to 08:30 but does not works if I choose 08:15 to 08:45


Comma vs semicolon depends on your system settings.

The cells in F3:BP3 only have 8:00, 8:30, 9:00 etc.

If you want to handle 8:15, 8:45 etc, unmerge those cells and fill the empty cells with 8:15, 8:45 etc.

Good to know about comma and semicolon, noted.

You are right, and my bad since the screenshot was taken before your first answer, but I did unmerged and filled the empty cells when trying your first answer so the issue is happening with cells unmerged and filled. Sorry I didn't mentioned it before.

You are helping me a lot, really appreciate it.


Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.


Here is the workbook with the modified rule for lunch breaks.

Thanks a lot! Works like a charm