Creating Dynamic Range Borders

**jaredmccullough** · 09-07-2012, 10:55 AM

I am looking to create a border based on a dynamic range. A list is generated from a data validation in cell E2 of a worksheet. The information begins populating in cell "A4":"E4" and down based open the item chosen in the data validation. I would like the information when it is populated to be bordered. I have found a couple macros but can not seem to get any of them to work properly. So far this is the one I have been working with.

Please Login or Register  to view this content.

It works creates borders but not in the desired spots. From cell A4:E4 and below based upon the entered information. I think this might be because it does not update with the data validation.

Moderator Edit:
@ jaredmccullough

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.

**BuckoAk** · 09-09-2012, 02:39 AM

The macro works at creating borders well, so to help me understand a little more at your final border look.

Can you give a little more specific info on the borders

Is this outline border only?
No internal vertical lines per row?
Outline and internal Horz lines only?

Maybe you can upload a sample workbook with Border examples of what you looking for.
Show different ranges with maybe 1, 6, 10 row(s) variations that have your final look of what you want.

**jaredmccullough** · 09-10-2012, 08:55 AM

Bucko Thanks for the reply.....I will just attach my workbook and explain.

Go to Sheet 3 "Manager Report". Choose an item from the data validation from E2. See how it populates from cell A4 to E4 and down. I want it to work dynamically based on the range of information returned. Essentially I want it to create a border around the cells that are being returned (Border each cell)
MOC Tracking Database.xlsm

**BuckoAk** · 09-11-2012, 02:43 AM

jaredmccullough,

Give this a go, let me know if this is what your looking for.

**jaredmccullough** · 09-11-2012, 09:31 AM

Bucko,

It works wonderfully but if you look at the example I provided the first three rows (Heading/Title Rows) are bordered in white and are constant. In the given revision these are not there. I was going to fix it but I didnt know for sure where in the coding is the appropriate spot to put it.

**watersev** · 09-11-2012, 10:05 AM

hi jaredmccullough, please check attachment. Try to change E2 value through dropdown on Manager Report. If data does not appear run code "EnablEvents".

**jaredmccullough** · 09-11-2012, 10:38 AM

watersev,

It seems to work like my desired effect but two issues:

First what exactly is the purpose of enableevents macro?

Second if you look at the one provided it returned cells(i, "B") to cells(x, "C") essentially returned dates to manager report sheet for some odd reason that changed in the one you provided but the coding was not changed?

**watersev** · 09-11-2012, 10:45 AM

1. As your code triggering is based on event it may happen that events will not be traced by the application so nothing would happen until you turn them on.
2. The Worksheet_change code for Manager Report has two changes:

Yours:

Please Login or Register  to view this content.

changed to:

Please Login or Register  to view this content.

Another two lines have been added (commented):

Please Login or Register  to view this content.

**jaredmccullough** · 09-11-2012, 11:26 AM

It seems that there is something in the coding that changes the returned data in Column C of the Manager Report Sheet. Originally it returned the dates from column B of other sheets but it is returning Column A of other sheets

**BuckoAk** · 09-11-2012, 12:05 PM

Copy and Replace the Following Code for Add_Dynamic_Border
It was an over sight on my part, the code below will fix it.

Please Login or Register  to view this content.

**jaredmccullough** · 09-11-2012, 12:20 PM

This works appropriately Bucko much appreciated. Is there anyway to get the borders for the first three rows to be white as in the originally provided example without to much extra work? If not this should suffice.

**BuckoAk** · 09-11-2012, 12:50 PM

Sorry about that jaredmccullough, I knew I should have downloaded a new file.
See my last post, I add additional code to the bottom.
Just copy and past in the new section and you should be good to go.

**Marcol** · 09-11-2012, 01:27 PM

Why dont you just use Conditional Formatting?
I suspect it is because you have merged cells in Rows 1:2, and you couldn't get C/F to work.

Get rid of them!

See this workbook

**jaredmccullough** · 09-11-2012, 02:15 PM

Thank you for all your help Bucko and everyone who gave their input

Creating Dynamic Range Borders

LinkBack

Thread Tools

Rate This Thread

Display

Creating Dynamic Range Borders

Re: Creating Dynamic Range Borders

Re: Creating Dynamic Range Borders

Re: Creating Dynamic Range Borders

Re: Creating Dynamic Range Borders

Re: Creating Dynamic Range Borders

Re: Creating Dynamic Range Borders

Re: Creating Dynamic Range Borders

Re: Creating Dynamic Range Borders

Re: Creating Dynamic Range Borders

Re: Creating Dynamic Range Borders

Re: Creating Dynamic Range Borders

Re: Creating Dynamic Range Borders

Re: Creating Dynamic Range Borders

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions