+ Reply to Thread
Results 1 to 10 of 10

Macro to set extra extra thick outer border for selected column and row:

  1. #1
    Registered User
    Join Date
    07-12-2014
    Location
    North Carolina
    MS-Off Ver
    Office 2007
    Posts
    79

    Macro to set extra extra thick outer border for selected column and row:

    Hello,
    I've been scouring the internet and tried multipe (I'd say more than 6 or 7 by now) different macros to do this without success.

    I'm trying to build a Macro (that I can put inside a different, bigger Macro), that will set a thick outer border for the row and column of the selected cell. This will only be to make visual tracking easier, so when I select a different cell, I'd like the thick outer border to be removed form the old cell, and placed in the new cell.

    I've seen lots of ways this can be done with color highlights, but the problem I have is that the worksheet I'm using relies heavily on highlighting different objects, and the macros that I've tried modify, remove and reset the colors I have pre-formatted (or need to add) into that sheet.

    Please be a little more detailed than usual, I've been struggling with this, but maybe there's some simple step I'm missing.

    I'm using Excel 2007.

    Thanks in advance,

    Chad

  2. #2
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    165

    Re: Macro to set extra extra thick outer border for selected column and row:

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    165

    Re: Macro to set extra extra thick outer border for selected column and row:

    try the attached... you'll need to modify
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-12-2014
    Location
    North Carolina
    MS-Off Ver
    Office 2007
    Posts
    79

    Re: Macro to set extra extra thick outer border for selected column and row:

    Guitarsweety,
    That's awesome, I really like how it only highlights the rows above and to the left of the cell. I'm guessing that if I want to incorporate that into an existing spreadsheet I'll click "view source" for that tab, and paste this part into the spreadsheet (with "Worksheet" and "Selection Change" selected from the dropdown menus), right?:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim RngRow As Range
    Dim RngCol As Range
    Dim RngFinal As Range
    Dim Row As Long
    Dim Col As Long

    Cells.Interior.ColorIndex = xlNone

    Row = Target.Row
    Col = Target.Column

    Set RngRow = Range("A" & Row, Target)
    Set RngCol = Range(Cells(1, Col), Target)
    Set RngFinal = Union(RngRow, RngCol)

    RngFinal.Interior.ColorIndex = 6

    End Sub

    After that where do I put the

    Sub BORDER()

    Selection.Borders.Weight = xlThick

    End Sub

    part so that instead of highlightin cells it will set the thick border?

    Thanks for the spreadsheet you attached, and thanks in advance for the clarification,

    Chad

  5. #5
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    165

    Re: Macro to set extra extra thick outer border for selected column and row:

    Yes Chad it's all done on the worksheet selection change event. Unfortunately it does remove any previous cell shades

  6. #6
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    165

    Re: Macro to set extra extra thick outer border for selected column and row:

    [CODE]v
    RngFinal.BorderAround (xlThick)/CODE]

  7. #7
    Registered User
    Join Date
    07-12-2014
    Location
    North Carolina
    MS-Off Ver
    Office 2007
    Posts
    79

    Re: Macro to set extra extra thick outer border for selected column and row:

    Sorry to be so picky, and I really appreciate you helping me, but I'm really looking for something that ONLY changes the border, and has no impact on the highlighting at all.

    Is that something that you think is even possible? If not just let me know and I can try to find some alternative.

    Thanks again,

    Chad

  8. #8
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    165

    Re: Macro to set extra extra thick outer border for selected column and row:

    Try the attached, I think this is more of what you're after.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-12-2014
    Location
    North Carolina
    MS-Off Ver
    Office 2007
    Posts
    79

    Re: Macro to set extra extra thick outer border for selected column and row:

    Hey Guitarsweety. I couldn't quite get this to work when incorporating it into my spreadsheet, but thanks for taking the time to put it together for me. I was able to find an alternative. I'll mark the thread as solved.

  10. #10
    Registered User
    Join Date
    07-12-2014
    Location
    North Carolina
    MS-Off Ver
    Office 2007
    Posts
    79

    Re: Macro to set extra extra thick outer border for selected column and row:

    One more question Guitarsweety, I tried the newest spreadsheet you uploaded, and it works, but now my Macro can't seem to paste anything into that sheet. Any tips? Please don't go crazy and rebuild anything, just let me know if you know an easy fix.

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 08-27-2012, 04:37 PM
  2. Remove extra border and put all border in last row of generated output
    By cruise.alter in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-05-2012, 01:21 AM
  3. extra hours adding money for each extra hour worked FORMULA
    By cynthiamcastro in forum Excel General
    Replies: 3
    Last Post: 06-18-2012, 11:27 AM
  4. Border for row, 10 extra rows with borders
    By anno1404 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-02-2011, 05:05 AM
  5. Need macro to add thick box border to certain cells - THANKS FOR THE HELP!!!
    By Jeff M in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-05-2011, 09:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1