Conditional Format a Table Column?

[Raysoc]

Hi I am trying to apply conditional formatting to a table column.

I import data from a MSSQL query into a table, the table could be variable in length (rows) but I want the conditional formatting to always apply to one column.
I tried setting the range in the conditional formatting to =Table_ExternalData_1[[#All],[Col1]] and even tried =Table_ExternalData_1[[#Data],[Col1]] but it refuses and always sets the absolute range and when I apply filters on the table to exclude some rows the range doesn't change because it refuses to acknowledge the table header as what I want.

I even tried named ranges and dynamic named ranges it always sets the range back to cell values not a table heading or range.. is this impossible what I am asking or am I missing something entirely simple?

Thanks in advance.

 

[wivory]

Make sure you select the option "Insert entire rows for new data, clear unused cells" in the query properties.

This solved an unrelated problem for me. Whether I had conditional formatting on the Table (i.e. =Table_Query_From...) or an encompassing range on the Worksheet, when I refreshed the query and the number of rows changed Excel 2010 was splitting the Conditional Format formula up into sections. The more refreshes, the more it became disjointed, with some ranges becoming invalid with #REF errors and some rows not being incorporated into the Conditional Format. I'd been manually fixing it for weeks.

But once I set the "clear" option this stopped happening. I know this is a long way down the track from your post, but thank you boulio!!