Conditionally format a large and growing table efficiently without making it slow/large?

[Elijah]

Hi all,

I have several large Excel sheets (tables) that I want to format conditionally, column by column. These are dynamic tables in the sense that they're updating daily/weekly with new rows. I have 100 columns and roughly 1500 rows at the moment.

In the past, I would just apply the formatting for entire columns (A:A, etc.), but I ended up with a file that was very large and which also seemed slow.

I suspected it was partly because of the color formatting, but I can't say for sure.

Now that I've improved my files otherwise and starting from scratch, I decided to format only the table content, i.e., H7:H1465. I thought this would also auto-update as my table grows in size, but that does not seem like it's the case. Please see attached picture.

Question:

1) Is it possibly to format a table in this way AND have it auto-update?

2) Perhaps H:H is OKAY and does not affect performance/size of the sheets negatively?

Proposed alternative solution:

3) Format say 1465 + 500 or 1000, i.e., H7:H2500. This should allow me to update or use a larger dataset without any trouble in a long while.

Thanks in advance for all help before I start fixing this issue.

Elijah

Formatting.png

[BadlySpelledBuoy]

If it's set up as a table it should copy the conditional formatting down as you add new rows. I've just tried and it does for me.

However, go to "File / Options / Proofing / AutoCorrect Options / AutoFormat As You Type" and make sure there is a tick in the box next to "Include new rows and columns in table".

BSB

[Elijah]

Hi, BSB,

Thanks for writing. I checked all those settings in options and they were ticked off correctly.

It does indeed seem to be the case when doing it manually.

I didn't consider it to be relevant, but I guess it is: my table/excel file is updated by loading data from an SQL database. I'm not quite sure how this process works.

But it does seem like the way this macro loads/adds data; the formatting is not copied further down...

Any other ideas?

Elijah

[Elijah]

Unless anyone can offer a better solution, I will probably format (numbers, text, etc.) ALL columns where standard does not apply, i.e., A:A. I imagine this shouldn't impact performance, yes?

Then, for conditional formatting, I will probably just extend H7:H1465 to H7:2000. This gives me two years extra data.

Because, I think if I do H:H on conditional formatting across 100 columns, it will probably impact performance, size and stability of the file, yes?

[BadlySpelledBuoy]

If you're using VBA to bring the info in then you can also use it to resize the table appropriately.

I'm travelling home at the moment but once I'm there and in front of a laptop I can give you code you can adapt to do just that.

BSB

[Elijah]

If you're using VBA to bring the info in then you can also use it to resize the table appropriately.

I'm travelling home at the moment but once I'm there and in front of a laptop I can give you code you can adapt to do just that.

BSB

That would be great.

I do use VBA to import data from an SQL database. It was not made by me and seems complex (to me). I'm not sure how easy it'd be to implement it in the existing code.

Best regards.

Elijah

[BadlySpelledBuoy]

Apologies for the tardy response, Elijah. It's been a hectic few days.

Could you post your existing VBA for importing the data (don't forget to use code tags!). With that I can show you what you need to add to make the table resize.

BSB