In Excel Gantt Chart: if more than one cell in a column is black, turn automatically the respective date column & rows in red

[motahar]

hi dear all.
happy new year to you.


please help me out.
i am stuck in organizing a gantt chart to show automatically the overlapping date of a student's training schedule.
the gantt chart is made to highlight the "rows of days cells" in black.


in a column if more than one cell get highlighted in black,
i need those black cells/column of that date
and the respective rows of the course to get highlighted automatically in red, to warn me that the respective
student is enrolled in more than one course where the date are overlapping,


the chart is attached in jpg in this link

https://www.mediafire.com/?lgxf6r617mgf9uh




thank you so much.

 

[motahar]

dear worf, thank you for your posting. i tried with your above guidance. but still its not working for me. i could not find out why.
if you could send me a link for your test work book please.

 

[Worf]

I am not sure how your Excel version, language and regional settings will translate it, but here we go; the conditional format is applied down to row 50.

https://dl.dropboxusercontent.com/u/52116836/CLASS OVERLAPPING.xlsm

 

[motahar]

please check your inbox i have sent you the link.

 

[Worf]

Hi
I just got the new file.
I am going offline now and will work on it tomorrow…

 

[Worf]

To do what you want, use the formula already mentioned. Test it first on a single worksheet cell to ensure the syntax is correct.
Apply the conditional formatting to the range B7:C50.

=AND(SUMPRODUCT(($B7<=$C$7:$C$50)*($C7>=$B$7:$B$50))>1,LEN($B7)>0)

 

[motahar]

(AA) with your this above guidance, now the formula is working in my worksheet. but the color for the overlapping dates are not changing with the color formatting done inside conditional formatting.

(BB) your first formula was working also for the color change for the overlapping dates, with the color formatting done inside conditional formatting. this you can observe as red color in the worksheet that i have forwarded in your inbox with the link on 18th jan 2017, posting number #7. thank you.

 

[Worf]

See if the test workbook helps, it seems to work correctly for me:

https://dl.dropboxusercontent.com/u/52116836/CLASS OVERLAPPING - 2ND_Copy-18JAN17.xlsx

 

[motahar]

thank you so much. its working good.
i had been typing and sometimes also tried by copy/paste of the syntax/formulas
but i think the cell references were getting changed while saving, i dont understand why.
i need to learn more on how to enter correctly these type of long complex syntax/formulas.
with lot of passions you kept helping me. thank you again.
if you have any youtube video tutorial on this regard or something more, will you advise the website/link please.

 

[Worf]

Indeed, it is easy to mess up with a conditional formatting formula; for example, if you click the formula and press a keyboard arrow, a cell reference appears…

When entering or editing a CF formula, I prefer to delete whatever is there and type again from scratch.
If you are interested in learning more and have some time for reading, consider buying a book, see one suggestion below.

From the book page:

• Demonstrates how to use all the latest features in Excel 2013
• Shows how to create financial formulas and tap into the power of array formulas
• Serves as a guide to using various lookup formulas, working with conditional formatting, and developing custom functions
• Shares proven solutions for handling typical (and not-so-typical) Excel formula challenges

Wiley: Excel 2013 Formulas - John Walkenbach

 

[motahar]

thank you so much.