1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: VBA in Excel: Automatically paint borders on data entry

Discussion in 'Business Applications' started by maxx_eclipse, Dec 21, 2012.

Thread Status:
Not open for further replies.
  1. maxx_eclipse

    maxx_eclipse Thread Starter

    Joined:
    May 28, 2007
    Messages:
    291
    Afternoon everyone!

    I am trying to get a little bit of code working such that when a user enters in some data in column A, it triggers code in VBA to paint a simple surround black border over the whole row, from column A to M. Here is what I have so far, but it doesn't seem to be working. I know I am missing something (if not a lot).

    Code:
    Dim i As String
    
    If ActiveCell.Column = 1 Then
    	If ActiveCell.Value <> "" Then
    	Range(Cells(i, 1),Cells(i, 13)).Borders.LineStyle = Excel.xlLineStyle.xlContinuous
    	Else
    	End
    Else
    End
    
    I appreciate any help! Thank you in advance!
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    Hi Maxx, you shouldn't forget to mention the version of Excel you're using

    Place this code in the sheet

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <= 13 Then
        If Target.Value <> "" Then
        Range("A" & Target.Row & ":M" & Target.Row).Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Target.Offset(0, 1).Select
        End If
    End If
    End Sub
    
     
  3. maxx_eclipse

    maxx_eclipse Thread Starter

    Joined:
    May 28, 2007
    Messages:
    291
    Hey Keebelleh, thanks for the reply and sorry for the late response; I have been away from my computer these past few days.

    I will test the code soon and let you know if I am having any issues.

    I forgot to mention I use Excel 2010.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1081813

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice