SOLVED

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)
Solution

@Tomas_Calvo 

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?

@Tomas_Calvo 

Use the formula

=$BT4=OFFSET(F$3,0,-ISODD(COLUMN()))

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

@Tomas_Calvo 

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.

@Tomas_Calvo 

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.

@Tomas_Calvo 

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

Thanks a lot! Works like a charm