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.
This event will work if one is entered anywhere in column A.
If you haven't used macros before you'll need to go to:
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 And Target.Value = 1 Then Application.EnableEvents = False Target.Copy: Target.Offset(1, 0).PasteSpecial xlPasteFormats Application.CutCopyMode = False: Application.EnableEvents = True End If: End Sub
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
Last edited by xladept; 12-10-2015 at 06:27 PM.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
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.
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.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 And Target.Value = 1 Then Application.EnableEvents = False Target.Copy: Target.Offset(1, 0).PasteSpecial xlPasteFormats Target.Offset(0, 1).Select Application.CutCopyMode = False: Application.EnableEvents = True End If: End Sub
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
Try this:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count = 1 Then If Target.Column = 1 And Target.Value = 1 Then Application.EnableEvents = False Target.Copy: Target.Offset(1, 0).PasteSpecial xlPasteFormats Target.Offset(0, 1).Select Application.CutCopyMode = False: Application.EnableEvents = True End If: End If: End Sub
Hello,
Putting the extra End If: did not work. It now results in the following error without formatting:
Capture.PNG
The "extra" End If is to cover this leading code:
Did you include that?If Target.Count = 1 Then
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks