Excel: Highlighting a whole column depending on date

Question

I have made my own Gantt chart in Excel, which is shown in this picture:

.

I would like to highlight the whole column (or until the last activity) depending on the date, as shown in the picture. I figured out how to highlight a single cell depending on the date, but not a whole column.

I don't know how to do it short of entering the date into the whole column, but I thought there would be something more efficient if conditional formatting is used.

Answer 1

If you'd like an entire range to conditionally format based on the value of one of the fields in the range, use an absolute cell reference. I couldn't figure out perfectly from your picture which cell contains the date, but I'll try to give you an example.

If you have dates in the Cells in Row 1, and you want to highlight the entire column when the row has today's date, the conditional format formula would look like below. (Assuming today's date is in cell C1, then the entire column from C1:C25 [the range upon which I applied the conditional format] will be highlighted).

Steps:

1. Highlight the entire range upon which you want to apply the formatting. In my example, I'm using range A1:H25
2. In the Excel Ribbon, select HOME - CONDITIONAL FORMAT - NEW RULE, then select USE A FORMUAL TO DETERMINE...
3. The formula will be =A$1=TODAY() [NOTE: We check cell A1 first because it is the first cell in our highlighted range. We choose $1 because the dates are always in Row 1. Change that to suit.]
4. Select the format you'd like to apply
5. Test it