Programmatically Need to have a BOLD / THICK Border of a Colour of Choice Dynamically

[e4excel]

Programmatically Need to have a BOLD / THICK Border of a Colour of Choice Dynamically

I have a requirement in which i need to have a perimeter which keeps on changing and I use Fill colour to change the colour in each block however since the Blocks keep on Expanding and Decreasing it becomes difficult to maintain or draw the Border.

I would appreciate if there was a workaround in which I get BOLD / THICK BORDERS and to achieve this through CONDITIONAL formatting is not possible as it does not have an option of BOLD or THICK borders..

Ex:

I would mention the Range in the Cells AA1 under the heading Column as A and in Cell AA2 under the heading Row 2 till AB1 as Y and ROW as 27..
The Colour of the Border would be mnetioned in cell AC1

Then this entire range i.e A2:Y27 should have a thick border of the specified colour ..

I hope this is possible in VBA..

Warm Regards
e4excel

[wamp]

Something like this you want or need?
Here you define the range in cells A1 and A2, and the color in cell A3 (note: the colourINDEX. as in 4 for green)

Please Login or Register  to view this content.

[e4excel]

It does work dear!

Great but since I am no good in VBA just a bit more help would be appreciated..

Can this made direct Spontaneous without running the Macro when we change the cell Address ( if its legitmate Address) and the Colours i.e.

Please if you can attach a File it would be very helpful as I have just got 2007 installed and not really well-versed with it...

[e4excel]

Just one more thing can we also keep the Line Style, Thickess etc dynamic too?

[wamp]

Here you go.
Also added a LOT of comments to the code so you can understand what each itsy bit does and change it for your need

*EDIT*
I forgot to change something in the worksheet_change before uploading.
Right-click on "Sheet1" and change the line that says:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Hope this helps

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of their post to show your appreciation.

[e4excel]

Wow this works absolutely brilliantly! and I want this ..and I will be marking the thread as solved..

But just one more thing as I see this in reality now, as before was not sure whether this would be possible and thanks to you...you have made it possible...

Just one more thing can a small addition be introduced such as Erasing the earlier border if we change the Cell Address ,as in my actual Application i would be Expanding or Decreasing the Area more than once so if I could just have the Existing Border dissappear with the advent of a new one,,,or a keep a choice of Erasing the earlier one in cell A4 before it displays a new one..

Maybe 1 or 2 lines of codes I think os for you..

Thanks a lot and also for putting comments for me to understand it..


God Bless

Warm Regards
e4excel

[wamp]

then you could do a "with" selection for example..
or, "if cell A5 contains "delete" then"

Anyway.. with the selection you want borderless, use this code.

Please Login or Register  to view this content.

cheers

[davesexcel]

Another alternative would be

Please Login or Register  to view this content.

This would remove all borders in a sheet, of course you could use a specified range instead

[e4excel]

Guys,

Can you please put up an attachment with everything in one with the earlier utility included..I am no good in VBA i can only understand when someone has written the code but to add or append I get a little bogged down..

I will just put the same file in which Wamp has made with a little Comments for me to explain what Im looking for to make it more easier for us?

(Sorr: Due to the Internet issue could not attach my File),.. please find it attached now

[wamp]

it's quite simple tho'
you know how to look at the macro from the explanation sheet. (by opening "module1"
then, to add another macro in the same module, simply type

Please Login or Register  to view this content.

the code in between could be one of the examples you get from us.
That, in combination with the comments I gave for the previous code, should give you a good start

Another way is to record a macro (for example selecting all cells and removing all borders), then stop recording, and then have a look at the code

:-)

[e4excel]

Dear Wamp,

I would ahve tried that however I wanted to keep a choice of Removing or Mainataining the border and writing a code on that basis in VBA is not my cup of tea..

My Problem is I have still not figured out as to why some coding is put in MoDule some in Sheets and some are Fucntions..like I do not know the differnce and therefore ask for direct files as I can understand the code-application fair enough..

As far as this file goes, its almost done but just wanted to make it extrmely Dynamic this pplication is going to be used to show Areas of Plots and therefore it required Borders..
Expansion and Contraction..

I dont know whether there exists a VLOOKUP kind of functionality in VBA as instead of numbers I wanted to use the Actual Colour Names maybe a 10-20 Odd ColourNames ina Dropdown for the Border colour...

Thanks a lot...