Conditional Format a Table Column?

Raysoc

Board Regular
Joined
Feb 10, 2009
Messages
227
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
I don't think you can use structured referencing in conditional formatting.
 
Upvote 0
I don't think you can use structured referencing in conditional formatting.

Okay that is fine so is there any way at all that I can have the conditional formatting be dynamic with named ranges or a formula or anything?

Thx
 
Upvote 0
Okay that is fine so is there any way at all that I can have the conditional formatting be dynamic with named ranges or a formula or anything?

Thx
You should be able to do that.

Can you provide some more detailed info as to what you want to format?

What is the range address (A1 notation) of the data you want to format?

What type of data is it? Is it text? Numeric? Could be both? Something else?

And what is the format rule?
 
Upvote 0
You should be able to do that.

Can you provide some more detailed info as to what you want to format?

What is the range address (A1 notation) of the data you want to format?

What type of data is it? Is it text? Numeric? Could be both? Something else?

And what is the format rule?

The data is percentages, I am using the default 3 color gradient Red for high and green for low, the column is C but the rows will always be dynamic depending on the week the report is ran or if the user changes the heading filters. I just want the range to be dynamic for what it applies the conditional formatting to based on changes to filters if possible but at the very least the # of rows needs to be scalable to the data pull.
 
Upvote 0
The data is percentages, I am using the default 3 color gradient Red for high and green for low, the column is C but the rows will always be dynamic depending on the week the report is ran or if the user changes the heading filters. I just want the range to be dynamic for what it applies the conditional formatting to based on changes to filters if possible but at the very least the # of rows needs to be scalable to the data pull.
Ok, so the data is numbers.

Let's assume the first data row is cell C2.

You can define this dynamic range...

=$C$2:INDEX($C:$C,MATCH(1E100,$C:$C))
 
Upvote 0
Ok, so the data is numbers.

Let's assume the first data row is cell C2.

You can define this dynamic range...

=$C$2:INDEX($C:$C,MATCH(1E100,$C:$C))

This still does not work with conditional formatting, whenever the formula is entered it calcs the range and hard codes it.

Extremely annoying learning there is abs no flexibility with CF.

Thanks anyways
 
Upvote 0
This still does not work with conditional formatting, whenever the formula is entered it calcs the range and hard codes it.

Extremely annoying learning there is abs no flexibility with CF.

Thanks anyways
Good luck! :cool:
 
Upvote 0
Make sure you select the option "Insert entire rows for new data, clear unused cells" in the query properties.

Then once you apply conditional formatting to entire columns of the query table, the range will adjust automatically on refresh, always referring to the entire column as desired.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top