# Conditional Formating for time scheduled

#### TFSRZR

##### New Member
I'm trying to create a schedule chart that allows me to see what periods of a range have been scheduled. I have been able thru conditional formatting to highlight the cells yellow for the scheduled time (start / end). I’d like to be able to have two different highlight colors. I tried various conditional formulas to try to get the time blocks to highlight black where Unit in column A is X and Yellow highlights for all other units , but couldn't get it to work. I have posted sample spreadsheet to one drive https://1drv.ms/x/s!Ai2z2uOS2mdJbIvl6UrlCSbhd3A

Thanks

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Try this.

Applies to \$E\$2:\$AL\$15
Black formula: =AND(E\$1>=\$C2,F\$1<=\$D2,\$A2="X")
Gold formula: =AND(E\$1>=\$C2,F\$1<=\$D2)

I have used
OR(AND(E\$17>=\$C18,E\$17<=\$D18),AND(\$C18>=E\$17,\$C18<=E\$17))

That will confirm the CELLS to use

Now you can ADD the X
AND(\$A18="X",OR(AND(L\$17>=\$C18,L\$17<=\$D18),AND(\$C18>=L\$17,\$C18<=L\$17)))

now use that as the formula in conditional formatting and use Black fill colour - stop if true ticked

then a 2nd rule
AND(\$A18<>"X",OR(AND(L\$17>=\$C18,L\$17<=\$D18),AND(\$C18>=L\$17,\$C18<=L\$17)))

BUT you have Dates in the title times - you need to remove all the Dates snf just have time

Last edited:
Thanks, it works!

Thanks thisoldman and etaf! It works!!

Glad to have helped. Thanks for letting us know the solutions worked.

you are welcome

Replies
3
Views
1K
Replies
0
Views
415
Replies
8
Views
748
Replies
1
Views
873
Replies
2
Views
816

1,196,485
Messages
6,015,472
Members
441,898
Latest member
kofafa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back