Forum Discussion
Conditional formatting based on a Table column header
Hi,
I ran into an odd behavior in Excel displayed in the following screenshot:
In column A, we have a set of dates ordered ascendingly (in the format dd/mm/yyyy) formatted as a table with a header.
I wanted to format the set of dates so that all the dates that are earlier than the current date will get formatted (highlighted in red in this case).
So normally you would use a conditional formatting rule based on cell value and be done with it. But I wanted to use a formula for conditional formatting to add more logic to the condition (omitted in the screenshot).
The formula should be as displayed in the conditional formatting window - `=A1<TODAY()` or `=$A1<TODAY()` - which uses the table header as a reference to test the values against the current date.
Problem is, the formatting seems to get "thrown off" by one row when using this method, as can be seen in the screenshot:
In column B, there are the actual results when comparing the first cell values in column A to the current date. These comparisons are displayed in column C.Column F displays the same set of dates formatted using the normal rule based on cell value.
=A2<TODAY()This rule returns the intended result. The rule must reference the leftmost cell of the first row of the applies to range. Since applies to =$A$2:$A$14 the rule must reference A2. For example if the applies to range is $D$7:$H$25 we would have to reference cell D7 in the rule for conditional formatting.
