Cond Formatting - dynamic applied range on template

[TroySmith80]

Is there a way to make the 'applies to' range dynamic to include all rows with data?

I have a template that people grab and make a copy each time they do a job. There is a macro that populates the 'working' sheet based on data in other sheets. So in the template, there is no data (other than header row) on the working sheet, but it does have conditional formatting. Right now, i have the applied range for the formatting set to the first 2500 rows ($2:$2500) but this is not ideal, most jobs require far fewer lines, maybe 400-1,000. On rare occasions, there may be a job that requires more than 2500 lines.

Can i put something in the 'applies to' field that will be a cleaner / more efficient way to get the cond formatting where it is needed and not have it applied needlessly?

Is there a reason (performance?) not to just apply the cond formatting to rows 2-10,000 even though i'll never need 10,000 lines? Possibly related is that i'm trying to keep the vertical scroll bar to the range of actually used cells, rather than scrolling down taking me to the 1 millionth row or whatever, which is often happens for reasons i haven't yet been able to pin down.

Thanks!

[TMS]

You can enter a Dynamic Named Range in the Applies to box ... but Excel immediately converts it to an absolute range (in my testing).

You shouldn't use excessive Conditional Formatting if you can avoid it as it WILL have a performance hit depending on the number and complexity of conditions. Note that deleting and inserting rows and/or columns can split the CF rules up and, over time, this can have a serious performnce hit.

You could make your data area into a Structure Table and have as many or as few rows in the Table as you like. Formatting, CF and formulae are automatically copied to new rows, so that might work for you.

[TroySmith80]

You can enter a Dynamic Named Range in the Applies to box ... but Excel immediately converts it to an absolute range (in my testing).

Yes, i meant to start by saying that I'm sure this question has been addressed and i DID spend a fair amount of time googling it but did not find a solution. I did read of others who had this same experience.

You shouldn't use excessive Conditional Formatting if you can avoid it as it WILL have a performance hit depending on the number and complexity of conditions. Note that deleting and inserting rows and/or columns can split the CF rules up and, over time, this can have a serious performnce hit.

I suspected that and have run into some performance issues on these sheets so i'm trying to do things the best way possible.

You could make your data area into a Structure Table and have as many or as few rows in the Table as you like. Formatting, CF and formulae are automatically copied to new rows, so that might work for you.

I hadn't heard of a structure table, i'll look into that, thanks.

[TMS]

See: https://support.microsoft.com/en-us/...9-6c94334e492c