Help inserting rows with correct formulas and conditional formatting

jenlp

New Member
Joined
Feb 7, 2012
Messages
1
Hello,

I am looking for some assistance please with a spreadsheet I am currently developing.

Each row in my spreadsheet represents a unique job and has a number of formula and conditional formatting behind it which I drag across the row to autofill all relevant cells (because of this horizontal autofilling many of the cell references in my formula are absolute).

I have created a macro which inserts a row at the top of the spreadsheet and correctly inserts all the different formula and conditional formatting. My issue is that the user wants to be able to manually insert rows at any place in the spreadsheet. When this occurs, the formula in the rows do not copy down into the new row and the conditional formatting goes haywire for both the new row and the one it was inserted above.

I am thinking that my best course of action may be to create an InsertRow macro but am not really sure how to put it all together.

Any assistance would be greatly appreciated.

Many thanks in advance.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

svkroy

Board Regular
Joined
Sep 12, 2009
Messages
178
Hello,

I am looking for some assistance please with a spreadsheet I am currently developing.

Each row in my spreadsheet represents a unique job and has a number of formula and conditional formatting behind it which I drag across the row to autofill all relevant cells (because of this horizontal autofilling many of the cell references in my formula are absolute).

I have created a macro which inserts a row at the top of the spreadsheet and correctly inserts all the different formula and conditional formatting. My issue is that the user wants to be able to manually insert rows at any place in the spreadsheet. When this occurs, the formula in the rows do not copy down into the new row and the conditional formatting goes haywire for both the new row and the one it was inserted above.

I am thinking that my best course of action may be to create an InsertRow macro but am not really sure how to put it all together.

Any assistance would be greatly appreciated.

Many thanks in advance.

Go ahead create an Insert Row macro and at the end of it call the conditional formatting macro to oversee the formatting aspects. To restrict users from hitting Ctrl+ to insert rows, disable this function using "Application.OnKey" method.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top