Unsure how my Macros have made my WorkBook over 20MB?

[donnabee]

Workbook Here - WARNING it is 20MB plus

I have been (attempting to) clean up my workbook this morning, it is really large and takes a while to open, save and then close.
I found some "solutions" online and tried them (compiling VBA, disabling macros, enabling again, deleting formatting not required etc)
It's not really an option to split the worksheets in the book and create another book.

The workbook takes a while to open, save and close again.

Have I done something weird with the Macros? Do I have things where they should not be?

 

[donnabee]

I have taken all of your suggestions and implemented them, and I thank you for taking the time to reply with solutions.

The workbook is used to assess flood damaged roads, using the greed/yellow/red traffic light system to denote the start, evidence and end of treatment.
I'm trying to streamline the process, before making this workbook every row was selected and coloured by hand and it took way too long.
With automating a lot of the processes I also hope to remove the human error portion of the assessment.

Please don't hesitate to let me know if you have any other suggestions, what you have offered so far has been incredibly sound and valuable.

 

[Michael M]

Glad to help
Make sure your CF is also only affecting the required rows.
I note it also went to 22000 rows, so could be cut back considerably

 

[Michael M]

I also noted that the deleting of the "BLANK" worksheet, reduced the file size by 26Mb
AND your CF is duplicated....get rid of a set of the g/y/r CF's

 

[donnabee]

My CF never behaves. It duplicates itself, adds rows - you name it, it randomly does it!
I've reduced BLANK drastically, and it has definitely helped. I can't believe I was blaming the macros!

 

[Michael M]

it looks like your applying the same formula to 2 sets of ranges. Why not simply use 1 set of ranges

So, just apply the formula to $A$2:$AD$20550....except not 20550, of course

 

[donnabee]

Trust me, I have only applied it once, I don't know how or why it duplicates itself.
I am constantly opening CF and deleting unwanted rules and editing the rules I have in place that have changed for no reason at all.

This morning I changed all 3 to:
=$A$2:$AD$5000

So I had 3 rules in CF at 8am, and that was all.
The screenshot I just took now at 15:48 shows a very different story.

I would LOVE to know why I suddenly have loads of rules, and why they start on weird rows and end at a range I didn't ask for.
My CF never stays as I made it, it ALWAYS changes and I can't make it stop.

 

[Michael M]

Have a read here, in particular the video near the start of the article

Fix Excel Conditional Formatting Problem Extra Rules

Fix Excel conditional formatting problems when extra rules are automatically created. How to remove duplicate rules and prevent them from happening

www.contextures.com

 

[Michael M]

I also think the problem is that you are trying to apply teach rule to an entire block rather than 1 row
=$A$2:$AC$20550
maybe apply the rule to
=$A2:$AC2
Then use format painter to copy to multiple rows.

 

[donnabee]

Thank you so much, I will definitely apply these changes and look into that video!