Auto Insert Formatted cells below

[mateow20]

Hello,

I am trying to find a way to insert formatted cell below a cell that had a value entered in it. For example, If you set up cell A1 to be a heading with box border, and cell A2 blank with a box border. What I am looking for is to be able to type a 1 in cell A2, and have cell A3 automatically format with a box border.

I hope that makes sense.

[xladept]

This event will work if one is entered anywhere in column A.

Please Login or Register  to view this content.

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Double Click on Sheet 1 to expose the Sheet Module and

paste the code under it and

you're ready to go

[mateow20]

Hello,

That works great. Just what I was looking for. One little modification that would be great, which I didn't specify before is, how to make the active cell default to the cell to the left of where the 1 was entered and not the cell that the formatting was made to?

Thank you.

[mateow20]

Hello,

Sorry about the duplicate reply. The modification I am looking for is, how to make the active cell default to the cell to the RIGHT of where the 1 was entered, instead of the cell that the formatting was made to?

Thank you.

[xladept]

Please Login or Register  to view this content.

[mateow20]

Hello,

Thank you very much. That works. Something I have noticed is that if at sometime i need to delete a row that was auto formatted I get a run time error.

Is there any way to avoid that?

Capture.PNG

[xladept]

Try this:

Please Login or Register  to view this content.

[mateow20]

Hello,

Putting the extra End If: did not work. It now results in the following error without formatting:
Capture.PNG

[xladept]

The "extra" End If is to cover this leading code:

Please Login or Register  to view this content.

Did you include that?