conditional format with "applies to" that changes

[kevinu]

Im trying to find a way to change the area that conditional formatting applies to automatically. I have a sheet that has data on it and I often add data to it from time to time and would like the conditional formatting to change with it automatically instead of me having to change the "applies to" range manually if possible.

for example if I have conditional formatting that applies to D2:AE200 and I add more data at row 201 can it see that was added and change the range.

I would just make the range larger than I need but i also use sort often and if the range is larger it sorts the empty rows as well.

[Norie]

You could create a dynamic named range and then use that for the 'applies to' range rather than a hard-coded range.

[dflak]

The easiest way to do this is to put your data into an Excel table. http://www.utteraccess.com/wiki/Tables_in_Excel.

One of the benefits of an Excel table is that it "copies down" formulas, formats (including conditional formats) and validation. So when you create the Conditional formatting, highlight the data (exclude the header) in the column. Then when you look at the conditional formatting later, one of the options is to show formats that apply to the table. These range will show the sheet values in the Conditional Format box, but internally it's linked to the table column so if you add or subtract data and go back, you will see the sheet addressed still match the table column.

P.S. Also what Norie said.

[Norie]

dflak

Always forget about tables for this sort of thing - far easier option than setting up dynamic ranges, in my opinion anyway.

[dflak]

I am a tables fanatic even when I don't need them.

[kevinu]

not sure a table would work with my setup but i'm also not familiar with tables. Here is an example of what I have. First page "working sheet" is where the data is put to look at. Second page "data" is where the data is. The data is constantly changing so I'm hoping to only change it in one place. In my version I have 3 other sheets that call on the data to use it. Im using =IF('data sheet'!A1:AB1="","",'data sheet'!A1:AB1) to pull the data from the data sheet to the "working sheet" automatically. Not sure if I'm explaining it right so here is the example.

[dflak]

A table could work, but in this case, it doesn't confer a big benefit since we need to cover both rows and columns. Norie's on the right track with a dynamic name here except that the range has to be found using VB.

So the following program uses LastCell to find the bottom-most, right-most cell to define the range to apply the formatting to. It also makes use of a helper cell on a helper sheet (Cell A1 on Sheet CF_Cell) that contains the validation you want.

It copies this cell and pastespecial formats to the computed range on the working sheet.

[kevinu]

very cool thanks

[Monimonika]

You could create a dynamic named range and then use that for the 'applies to' range rather than a hard-coded range.

Do the Office 365 & 2016 versions of Excel really allow for this? I only have Excel 2010 on hand, and that automatically converts any (dynamic or not) named range into an absolute reference range in "Applies to".
I tried looking this up, but got nothing in the way of a firm confirmation. Anyone with newer Excel up to actually trying this suggestion out and seeing if the named range remains unconverted?

[dflak]

Named dynamic range worked at least as far back as Excel 2003. That's when I used them.

I wound up effectively creating a named dynamic range using VB in this case because I didn't know which row had the most amount of data. In a named dynamic range you usually determine how many rows and/or columns to include in the range using COUNTA - this assumes you pick the longest column or row and the the data are contiguous (no blank cells). In the case of your data, I needed to use LastCell to make sure I caught the longest row and column.

Here is some information on dynamic ranges. They come in handy for formulas, data validation drop-down lists and dynamic charts.
http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges

[Monimonika]

Named dynamic range worked at least as far back as Excel 2003. That's when I used them.

Interesting how no one with a version of Excel higher than 2010 can respond whether or not they were able to use a dynamic named range (or even a simple named range) in the "Applies to" field. Even the one who first suggested it hasn't confirmed it with their own Excel version.
From my online search elsewhere, all I get are threads where the dynamic named range gets presented as the solution, usually followed by the questioner saying it doesn't work in the "Applies to" field, and then the threads end there.

[dflak]

The only reason a conventional named range would not work here is because there is not a convenient formula to find the longest row and the widest column. Normally, COUNTA accomplishes this

=OFFSET(SheetName!A1,0,0,COUNTA(SheetName!AA),COUNTA(SheetName!1:1))

However, we don't know if column A is the longest column or if it has gaps. Similar logic applies to using row 1.

[jeffreyaj]

I am using Excel 2013 and named range doesn't work in the "Applies to" field.

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread