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

motahar

New Member
Joined
Dec 31, 2016
Messages
10


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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi and welcome to the Board

Can you post a link to the workbook, so I do not need to rebuild the chart?
 
Upvote 0
Use the following conditional formatting formula on range B7:C16 to highlight overlapping courses.
Is this kind of warning enough? For more refined formatting, we will need VBA.

=SUMPRODUCT(($B7<=$C$7:$C$16)*($C7>=$B$7:$B$16))>1
 
Upvote 0
thank you so much. you are great. it is working good. this much warning will be enough to work out the schedule.
am i correct that when the number of rows will increase to for example, to 20, then in place of $C$16 & $B$16 it should be $C$20 & $B$20.

 
Upvote 0
That is correct.
If you use the following formula that refers to a wider range and apply the rule to it, there is no need to edit the conditional formatting again.

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

<tbody>
</tbody>
 
Upvote 0
thank you so much for your continuous support. i could not get your second formula into working in the conditional formatting.
if you please advise me with some guidance. so i can figure out my mistakes.
 
Upvote 0
Sorry, there is a typo on that formula…
If you still have problems with the corrected version below, I can post a link to my test workbook. Apply the conditional formatting to the range B7:C100.
Note that depending on what country you are, formula separator can be “;” or “,”.

condf.JPG
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top