+ Reply to Thread
Results 1 to 13 of 13

Auto change borders to thick borders

  1. #1
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Auto change borders to thick borders

    Hi All,
    From what I understand it is impossible to get Excel to conditionally format borders to be thick, and I was looking to see if anyone knows a way around this problem. Right now we have our sheet formatted to be color coded based on the number of instances and borders placed around the row of those instances. As in the attached example, we copy varying data (on tab 'CopyThis') into a formatted blank page (Tab 'Current') and the result is the colors and borders in the locations we want, but we we looking for a thick border instead of the regular thin one (example on 'Goal' tab). Any help would be greatly appreciated, thanks!
    Last edited by Pierce Quality; 08-06-2013 at 08:48 AM.

  2. #2
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Auto change borders to thick borders

    Bumping along

  3. #3
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Auto change borders to thick borders

    Bumping along

  4. #4
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Auto change borders to thick borders

    Bumping along

  5. #5
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,407

    Re: Auto change borders to thick borders

    Hi Pierce,
    It is only possible with a macro.
    Copy this macro in your SHEET module. Double click on cell A1 to activate the macro.
    Note : you can remove the conditional formatting for the horizontal line.

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Auto change borders to thick borders

    Quote Originally Posted by GC Excel View Post
    Hi Pierce,
    It is only possible with a macro.
    Copy this macro in your SHEET module. Double click on cell A1 to activate the macro.
    Note : you can remove the conditional formatting for the horizontal line.

    Please Login or Register  to view this content.
    Thank you very much! I changed the format to a macro enabled workbook, but am unsure exactly how to copy the macro properly, Im very new to VB, only having completed a quick 'Hello World" intro a few years ago. Could you please advise me on the procedure to copy the macro into my sheet module? Thanks!

  7. #7
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,407

    Re: Auto change borders to thick borders

    From Excel : ALT + F11 to open the VBA editor
    You should have a listing of VBA projects in the left pane, if not, press CTRL + R to show it
    Find your file in the list and under "Microsoft Excel Objects", double click on your sheet "Sheet1 (Current)".
    Copy the code in the white box on the right.

    See picture...
    Attached Images Attached Images

  8. #8
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Auto change borders to thick borders

    Thank you very much for the explanation! I was able to get the macro to work when I double click on A2, but I was wondering a couple of things; is it possible to get the macro to activate with out double clicking? When I copy my info on to our page there will always be an instance in E2 so maybe it could work off of that? Also, is it possible to have the vertical outside borders a thick border like the horizontal lines? Thanks again!
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,407

    Re: Auto change borders to thick borders

    Hi Pierce,
    I changed the macro. It will launch automatically when cell E2 is changed.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Auto change borders to thick borders

    Quote Originally Posted by GC Excel View Post
    Hi Pierce,
    I changed the macro. It will launch automatically when cell E2 is changed.

    Please Login or Register  to view this content.
    Thank you very much, this is what we were looking for!

  11. #11
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Auto change borders to thick borders

    Hi All,
    As a late add in, we were wondering if it is possible to get the two vertical column lines that stick out on the bottom after the information is pasted in to not appear. Ive tried without success so far and was wondering of someone could point me in the right direction. Its a very minor detail but one my boss would like to see resolved
    Thanks!

  12. #12
    Registered User
    Join Date
    04-01-2013
    Location
    bethesda
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Auto change borders to thick borders

    i'm in a similar situation with the conditional formatting limitations and thick border style. I'm looking for code that will look at each row for certain specific names in Column A (John doe, jane doe, micky mouse, minny mouse) and if the one of those names are in column A, then the entire row is formatted with a Thick Top Border.
    I am using the following code to Bold and shade the row, but it wont make the top border thick. Any suggestions would be greatly appreciated :-)

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=IF($A1=""John Doe"", TRUE, IF($A1=""Jane Doe"", TRUE, IF($A1=""Micky Mouse"", TRUE,IF($A1=""Minny Mouse"", FALSE))))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
    .Bold = True
    .Italic = False
    .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = 0.799981688894314
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThick
    End With
    Selection.FormatConditions(1).StopIfTrue = False

  13. #13
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,407

    Re: Auto change borders to thick borders

    Hi Powercell99,
    As per forum rules, next time please start your own thread and use code tags when you post code.
    To answer your question, try this :

    Please Login or Register  to view this content.
    GC Excel - My blog

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

+ 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. [SOLVED] Creating thick borders around rows
    By oneillp1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-26-2013, 11:45 AM
  2. Excel vba puzzle -- Thick or double cell borders significantly slow vba code
    By skappy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-01-2012, 06:55 PM
  3. Format Borders - Thick Outside - Thin Inside
    By mccrimmon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-30-2011, 10:51 AM
  4. Thick Borders
    By OfficeBitty in forum Excel General
    Replies: 10
    Last Post: 11-28-2006, 11:16 AM
  5. Conditional Formatting - Thick Borders?
    By fedude in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-17-2006, 05:35 PM

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