Excel Conditional Formatting for a Worker Schedule

Question

Come to a halt and need help. We have a schedule of personal with job names, dates, and etc. The calendar year dates are to the right of that information with each cell represents a day of the year. I need the cells (which represent a date of the year) to fill in when a date is typed in columns G and H. (similarly to K and L) Now with the below formula I can get 1 cell to fill in with 1 color but isn't cutting it.

=AND(G16>=DATEVALUE("1/5/2015"),H16<=DATEVALUE("1/5/2015"))

I need the sheet to do the following:

1. I need the cells to the right which represent a date of the year to fill in with a color that corresponds to multiple colors based on what number is typed in on columns F or J. Those colors correspond to what is shown in the top left of sheet. i.e. represents whose job that is.
2. I need the cells off to the right to fill in; equal to and between the dates typed in on columns G and H with the color that is typed in columns F or J. (similar to K and L) i.e. shows the date of the project.
3. With the date value typed in merged cells I3-4 I need the names in column D to highlight that do not have a date value filled in; in which that date falls. i.e. representing that person is free to start a project on or after that date typed in merged I3-4.
4. With the date that is shown in merged E3-4, need the corresponding column on the right to highlight from row 10 and below to a certain cell. i.e. indicating todays date.
5. When there is an overlap of project dates column G = K or H = L then need to have a different fill appear showing overlap.
6. When there is date missing but a date in the adjacent date column then fill in with the same fill as above. Fill to go all the way across the rows to the end of the year cell. example: column G has a date but column H does not and similar for K and L.

I have manually shown what I need it to do.

Answer 1

Hi,

Shading a cell with a specific color (same as that of another cell) is not possible using only formulas.

You want to shade the cells based on values 1 or 2 .. given by the user in column F & I . And you want that this color should be the same color as given in cells D3, D4, etc.   This can be achieved only by writing a macro. Speak to someone who can write a macro for you on this.

Some of your other points can be done by using conditional formatting - however this would require more clarifications from your side.

Your requirement given in point no. 4 can be achieved using conditional formatting as shown in the acreen-shot pasted below.

Pls try and see if this works at your end.

We can then try to solve another requirement of yours.

Hope this is Helpful.

Answer 2

you may try with professional level forum for Excel, check the below link to post there,

https://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel

Answer 3

Here is the link. Let me know if you need it another way.

https://onedrive.live.com/redir?page=view&resid=F79AF3F0C7F1A82A!116&authkey=!AEurChUKYJ4LWjA

Answer 4

1. Can you share your xlsx file on OneDrive pls ?
2. Try to give your 6 points (which you have listed) with some examples and with desired results.