# 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

hi dear all.
happy new year to you.

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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Worf

##### Well-known Member
Hi and welcome to the Board

Can you post a link to the workbook, so I do not need to rebuild the chart?

#### Worf

##### Well-known Member
I got the file and will work on it as soon as possible...

#### motahar

##### New Member
thank you so much.

#### Worf

##### Well-known Member
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

#### motahar

##### New Member
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.

#### Worf

##### Well-known Member
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>

#### motahar

##### New Member
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.

#### Worf

##### Well-known Member
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 “,”.

Last edited:

Replies
3
Views
1K
Replies
29
Views
2K
Replies
1
Views
553
Replies
0
Views
1K
Replies
0
Views
1K

1,195,853
Messages
6,011,969
Members
441,658
Latest member
Carlos O

### 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