+ Reply to Thread
Results 1 to 16 of 16

Outside border change color

  1. #1
    Registered User
    Join Date
    04-02-2017
    Location
    Bakersfield, california
    MS-Off Ver
    2010
    Posts
    26

    Outside border change color

    Excel forums question.xlsx

    I have the following issue. From the attached I want to be able to have one cells border change to a different color so it is easy to see.
    On the attachment if the Pretreatment calcium is 5.5 and the Na+Mg is 2.5 then I want the cell which is now in blue(j21) to change its border color.
    The reason I want just the border color changed is because I have four different fill colors in the cells on the chart. This chart does not have the fill colors on it though.
    Or any other way to easily find the number on the chart without going down and across the axis.

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

    Re: Outside border change color

    Enter your values in F11 and D36 and then run this macro:
    Please Login or Register  to view this content.
    Change the ColorIndex number, 3 in this case, to a number that will give you the desired color.
    Last edited by Mumps1; 09-07-2017 at 02:07 PM.
    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 Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Outside border change color

    Hello DanS52,

    I would go without VBA and use Conditional Formatting as shown in the attached sample Workbook.

    CF Formula would be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Quick effortless and simple!

    Regards.
    Attached Files Attached Files
    Last edited by Winon; 09-07-2017 at 03:30 PM. Reason: Replaced sample File
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

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

    Re: Outside border change color

    @Winon: I believe that the formula approach would work only for those 2 values. I assumed that those values can change and so I proposed a macro.

  5. #5
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Outside border change color

    @ Mumps1,

    Good point. Thank you!

    TBH I never even bothered to give those two values a second glance.

    So we just change the CF Formula to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I have also replaced my sample Workbook in Post# 3.

    Kind Regards.

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

    Re: Outside border change color

    @Winon: Thank you for the feedback. The formula approach is very efficient.

  7. #7
    Registered User
    Join Date
    04-02-2017
    Location
    Bakersfield, california
    MS-Off Ver
    2010
    Posts
    26

    Re: Outside border change color

    Thank you guys for the responses. They work but I forgot the numbers that populate the two cells f11 and e36 will not exactly match the table numbers most of the time. Therefore, I need the closest match. Also, because all my cells will have a fill color what else other than a border color could I do to make the cell standout?

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

    Re: Outside border change color

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

  9. #9
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Outside border change color

    Thank you for the feedback DanS52,

    Therefore, I need the closest match
    We have no idea what criteria you apply to determine the closest match. You do say however that you have about four Colors which shows up in Cells based on conditions you have set.
    So let us use that as a point of departure to try a new solution, with the Code as shown below;

    Please Login or Register  to view this content.
    Please test the attached sample Workbook, and let us know if we are closer to a solution for you.

    Regards.
    Attached Files Attached Files

  10. #10
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Outside border change color

    Hi DanS52,

    To follow up from my last post, I believe you might like the solution in the new attached sample Workbook. To post the full code as an indication of what I did, will take up too much space, but I do think that the preceding posts addresses the issue at hand clearly enough.

    Regards.

  11. #11
    Registered User
    Join Date
    04-02-2017
    Location
    Bakersfield, california
    MS-Off Ver
    2010
    Posts
    26

    Re: Outside border change color

    Thanks for the response Winon. I have attached the program I am working on so you can get a better picture.
    Really all I want to do is find the cell within the chart based on the x and y axis numbers, before treatment and after treatment.
    That way I can quickly look over at the chart and see what the two cells are.Excel charts 2.xlsm
    Hope this helps. I appreciate the help. As you can see all the cells already have a background color and trying to find the
    x,y intersect is a little hard.

  12. #12
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Outside border change color

    Thank you for the feedback DanS52,

    Since we still have no idea what criteria you apply to determine the closest match, it may be better if you select the X- and Y-Axis, in Dropdown Cells, D33 and E33, and with Conditional Formatting, the chart will automatically place a black border around the relevant cell in the chart. At first I did not like it, but the more a tested it, it became increasingly easier to spot the Cell from the others!

    That is as far as I can help, and I hope you will find it in order.

    Regards.
    Last edited by Winon; 09-11-2017 at 05:41 AM.

  13. #13
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Outside border change color

    Hello DanS52,

    Attached please find yet another option.

    Regards.

  14. #14
    Registered User
    Join Date
    04-02-2017
    Location
    Bakersfield, california
    MS-Off Ver
    2010
    Posts
    26

    Re: Outside border change color

    Thanks Winon
    I really like how that shows up with the background colors.
    I still wish it could be done automatically. With the data validation method
    you propose, I guess they could select the closest numbers then.
    Thanks for you help. I will show this to the guys using it.

  15. #15
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Outside border change color

    Thank you DanS52,

    Let's hope for some positive feedback.

    Regards.

  16. #16
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Outside border change color

    Hi DanS52,

    Thank you for the kind words and the Rep. I really appreciate it!

    Kind regards.

+ 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] Click oval option button to fill/unfill color, also change border color to match fill
    By Rocky2013 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-07-2015, 08:29 PM
  2. Replies: 1
    Last Post: 11-24-2012, 12:38 PM
  3. Change label border color at runtime
    By timspier in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2006, 02:53 AM
  4. [SOLVED] Border color doesn't change.
    By Rusty in forum Excel General
    Replies: 3
    Last Post: 03-10-2006, 01:45 AM
  5. how to change form border color?
    By dpenny in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-31-2006, 09:44 PM
  6. Change the border color used by Change Tracking
    By Jim Stoaks in forum Excel General
    Replies: 1
    Last Post: 01-20-2006, 05:40 PM
  7. Replies: 3
    Last Post: 11-02-2005, 07:55 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