Adapting a macro of a previously solved question (not sure how else to describe this)

[3smees23]

Hi,

Some time ago several people helped me out by adding formulas and macros to the attached sheet. However, i want to be able to add more products, i.e. instead of 3 large red boxes I want 4.

I have tried copying the box myself and looking at the macro but I'm not sure how to change it. The macro has already been written and just needs adapting.

Regards,

Sam

[Bernie Deitrick]

You need to insert the fourth set of rows - the sheet is protected, so I cannot. Then change the module code to this - I think it should work... though you will need to check the formulas.... since I don't fully understand what you are doing with them (I could not test the code since the worksheet is portected.....)

Please Login or Register  to view this content.

[3smees23]

Thanks for your help Bernie! I have just tested it and the majority works.

The only part that not working is the conditional formatting. A bit of background, this sheet is a takeoff spreadsheet. We can add/remove floors depending on how big the project is, each table is a continuation of the one before, this enables me to select up to 36 different bits of kit per floor.

The conditional formatting in place is as follows:

1) If the network power for each floor is below 80 then the row on each 4 tables should turn red (calculation in column P on the takeoff sheet)

2) If the draw of each floor exceeds 2000 then then the row on each 4 tables should turn orange (calculation in column Q on the takeoff sheet)

3) Lastly, each alternate row is grey - this can be removed as it's no longer required.

So the problem remaining is that the conditional formatting on the newly inserted table (3rd one down) is not working. If you add a floor (by clicking the button) and type “1” in G15 then you will see that the red disappears on each table (except the 3rd table as it’s not working). This is because there is now a power value of +120 for that new floor

Hopefully this makes sense. I appreciate your help. Also I removed the password and saved it, when I go back into it, it's protected again!! So my password is "sam".

Thanks again for everything!

Sam

[Bernie Deitrick]

Your CFs referenced the wrong row for that table.

0xxxx_0 - Pricing Tool REV 8 test.xlsm

[3smees23]

There are still problems with the CF. Have a look at the attached.

I have entered equipment into table 1 yet in table 3 any floors that have been added, the formatting is still in red. If you add and remove a few floors the totals get messed up as well.

I think somewhere in the macro said for the CF rules to track down to added floors.

Hope this is a good enough explanation.

Thanks

[3smees23]

Can anyone help with this?

Thanks

Sam