Conditional Formatting based on Excel Table header

Question

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?

When Excel creates a table the headers are text and not numbers. Therefore when comparing numbers to text, text is always greater than any number. Also Conditional Formatting does not work with structured references directly, One can use INDIRECT to refer to structured references, but the Date as Text problem still remains. — Scott Craner
– Scott Craner, Commented Mar 18, 2021 at 14:45
You can try: =AND(--INDIRECT("Table1[@Headers]")>=$A3;--INDIRECT("Table1[@Headers]")<$B3) not sure if that will work or not. — Scott Craner
– Scott Craner, Commented Mar 18, 2021 at 14:47
I tried, but it seems to be as you said, and it is treated as text. — Mehmet Maraz
– Mehmet Maraz, Commented Mar 18, 2021 at 14:52

basic · Accepted Answer · 2021-03-18 15:11:13Z

5

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)

answered Mar 18, 2021 at 15:11

basic

12k2 gold badges15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Conditional Formatting based on Excel Table header

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related