Gantt Chart using conditional formatting

[Sriram Iyengar]

Hi All,

I am trying to create a GANTT Chart in excel (Sample attached) & it seems to work perfectly for the first Row.

1) Basis the month of delivery updated in Column I, the corresponding cell in the range I2: U2 gets highlighted

When I copy paste the formula for the rest of the rows, it doesnt work. I removed the $ sign but it still does not work. What am I doing wrong?

 

[vletm]

Some of Your I-column values are text ... some are dates = a challenge.
Could You use only dates?
... if so then use formula =TEXT($i2,"mmm") = J$1 to find dates-month and text-month combinations.

 

[Gateway2026]

The issue is with the dollar signs in the conditional formatting formula. When row 2 works correctly, the formula is =$I2=J$1. The $I locks column I (so it always reads the Month of Delivery column), the 2 has no $ so it shifts down row by row, the J has no $ so it shifts across column by column, and the $1 locks the reference to row 1 (the month headers). This combination of mixed references is what makes the formula work across the entire grid.

When you copy-pasted the conditional formatting to row 3, Excel created a separate rule with formula =$I3=J1, dropping the $ before the 1. That means the row reference to the header is now relative, so for row 4 it would look at row 2 instead of row 1, for row 5 it would look at row 3, and so on — drifting further off with every row.

The fix is to delete both per-row rules and replace them with a single rule applied to the entire range $J$2:$U$68 (adjust the last row number to match your data), keeping the formula exactly as =$I2=J$1. You only change the "Applies to" range, not the formula itself. Excel will handle the row-by-row and column-by-column shifting automatically from that one formula.