Auto Border if Text is Present

  • I think this has been asked before, I had searched the forum but that solution did not help me.

    Attached is the sheet wherein Sheet1 is the data I have
    I would like a VBA code so that a border is applied from column A to the right end till the last column. ( The end result is given in sheet2)

    My data can change to any number of columns or rows, and accordingly border should be applied

    I tried VBA code selecting column B where text is present, and apply borders from column A to last column, But it did not work.

  • Hi..

    Here's something you can adapt... it just puts a border around every cell containing a constant on the Sheet change Event.

    So if are entering anything other than a constant (likeFormulas).. then this one is no good. (Paste it into your sheet code)

    Private Sub Worksheet_Change(ByVal Target As Range)
     With Cells.SpecialCells(2)
      .BorderAround ColorIndex:=1, Weight:=xlThin
      .Borders(xlInsideHorizontal).LineStyle = xlContinuous
      .Borders(xlInsideVertical).LineStyle = xlContinuous
     End With
    End Sub

    Another option would be to use the Range.Areas Property and loop through each Area in your used range and apply a border to the whole area similarly to how it is applied above.

    Depending on how much data you have.. you could either put it again on the sheet change event or have a button to press that applies it at the users will.

  • Actually the borders are not applying as I would like to have
    As mentioned in my query the borders should apply from column A, ( even if there is no text )
    Same for the Row below the Alphabet C

    I have attached sheet for easy reference in my first post, wherein there are 2 sheets present one without borders and another exactly how I wish to have

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!