CODEWITHSUNDEEP
excelheaderformattingconditional-statements
Mehmet Maraz
Mehmet Maraz

Reputation: 53

Conditional Formatting based on Excel Table header

I want to highlight the cells in the month columns (C-F) if the dates in column A and B are in these month. The conditional formatting is working fine as you can see in the picture. But the formula in the conditional formatting refers to the dates in row 1. If I refer on the table headers, it stops working. The format of the table headers is DATE.

=AND(C$1>=$A3;C$1<$B3) is working, which refers on cells in row 1 outside the table

=AND(C$2>=$A3;C$2<$B3) isn't working, refering on the table headers in row 2

=AND("Table1[@Headers]">=$A3;"Table1[@Headers]"<$B3) also not working with structured references to the table headers

Is my structured reference wrong?

enter image description here

Upvotes: 2

Views: 2826

Answers (1)

basic
basic

Reputation: 11968

You can't use table references in CF, but you can refer to sheet ranges as usual. In CF use formula:

=AND(DATEVALUE(C$1)>=$A2;DATEVALUE(C$1)<$B2)

enter image description here

Upvotes: 4

Related Questions