+ Reply to Thread
Results 1 to 12 of 12

Draw a horizontal line across a range when column value changes.

  1. #1
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    315

    Draw a horizontal line across a range when column value changes.

    Hi,

    I have a range of cells in B6:BJ500. What id like to do is have a thick dashed horizontal line drawn across the range when the value in Column G changes. See below for a rough idea.

    Column G

    A
    A
    -----------
    B
    B
    B
    -----------
    C
    C
    C

    Currently i have this done via conditional formatting, but im thinking it might be quicker with a macro? Any ideas, views welcome.
    Last edited by Barking_Mad; 03-04-2014 at 05:48 AM.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7,418

    Re: Draw a horizontal line across a range when column value changes.

    Try:
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    315

    Re: Draw a horizontal line across a range when column value changes.

    Hi,

    Thanks Mumps, that works well. Just one problem - If i then change the information in the cells and re-run the macro, the horizontal lines from the previous macro are not removed, just new ones added.....is there a way to amend so it changes them back to your standard thin black line?

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7,418

    Re: Draw a horizontal line across a range when column value changes.

    Try:
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    315

    Re: Draw a horizontal line across a range when column value changes.

    Hi Mumbles

    That very nearly works, when i first run it, it puts the dashes in perfectly but then puts the normal thin lines outside the range (B6:BJ500), then when i run it again with values removed in the column G, it puts the thin lines back in correctly, but again I presume its re-drawing the lines outside the range..... Please note i changed the bold from your above code as it was drawing no lines

    Please Login or Register  to view this content.

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7,418

    Re: Draw a horizontal line across a range when column value changes.

    The line
    Please Login or Register  to view this content.
    is designed to remove all bottom borders in your sheet so that the sheet starts fresh before adding new borders after you change your data so you should leave it unchanged. When you say
    with values removed in the column G
    that means that there may be blanks in that column. Is this correct?

  7. #7
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    315

    Re: Draw a horizontal line across a range when column value changes.

    Ah sorry. My bad. However as per your original code it removes all lines on the sheet (horizontal and vertical) except the dashed ones, which are correct.

    What would ideally happen is:

    1) Values are entered in column G.
    2) The macro runs and horizontal dashed lines are inserted in the range (B7:BJ500) where the values change in column G. (this worked in your original response).
    3. I'd then like to be able to remove the values in column G, run the macro and have the lines revert back to standard thin black lines (ie before the macro was run).
    4) This must only happen in the range (B7:BJ500)

    Does this help. Thanks for your patience.

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7,418

    Re: Draw a horizontal line across a range when column value changes.

    The code below assumes that the cells G1:G6 are blank. If they are not blank, the code will have to be modified slightly. Please let me know.
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    315

    Re: Draw a horizontal line across a range when column value changes.

    Hi,

    No cells G1:G5 have information in them - well G5:G6 do, the others are empty....

  10. #10
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7,418

    Re: Draw a horizontal line across a range when column value changes.

    OK. Try this:
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    315

    Re: Draw a horizontal line across a range when column value changes.

    Mumble, you're a star, thank you so much for that

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7,418

    Re: Draw a horizontal line across a range when column value changes.

    My pleasure.

+ 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. Adding horizontal line to VBA column chart
    By the_adam in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-04-2013, 09:56 AM
  2. how to extend horizontal line on column graph
    By bos1234 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-22-2013, 10:54 AM
  3. How to draw an horizontal bar showing a range of values?
    By kaahja in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-25-2012, 05:10 PM
  4. Excel 2007 : How to draw a horizontal line on a Chart?
    By ThomasHaller in forum Excel General
    Replies: 3
    Last Post: 12-06-2011, 06:00 AM
  5. How to draw an line chart starting at column 1
    By Xaver Hinterhuber in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 06-06-2005, 04:05 AM

Tags for this Thread

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