Change cell border & outline in a macro

**namz** · 05-31-2008, 05:55 AM

Guys,

I have an excel sheet that is pulling data from other sheet based on some cretaria...i want a macro that when ran on this excel fixes column width , makes cell border as "dotted" and outline the area with Thick Outline.

I am attaching a sample sheet with raw worskeet (unformatted) and one formatted which i did manually.

Columns in worksheet will remain same but rows can change....can any1 provide me with a macro for this plz...

this is what i need ,

Column A width to be 31.00
Column B,C,D to be 11.29
Column E to be 7.00

Cell Border to be dotted,and thick box border around rows in Column B,C,D,E where columns rows has data.

**royUK** · 05-31-2008, 07:09 AM

Get the basic code bu recording a macro. the generated code can then be edited.

**namz** · 05-31-2008, 08:25 AM

Originally Posted by royUK

Get the basic code bu recording a macro. the generated code can then be edited.

thats the code...and dont worry abt column width...i fixed that...

Please Login or Register  to view this content.

**namz** · 05-31-2008, 08:30 AM

now what i want is that macro shud search numeric values in B,C,D,E and than format there cells , once cells are formatted rows in colum B,C,D,E with numeric values shud get this THICK BOX BORDER as shown in above macro or screenshot attached before ...my sheet is divided into 2 parts "INTERNALS" and "EXTERNALS" . and i have mentioned this columnA , so a cell in "A" displays "INTERNAL" (i.e. A2 fixed ) where INTERNAL sites end , a cell in rowA displays "EXTERNALS" )not fixed as it depends on INTERNAL sites rows which can change) and all external data falls under that. I need seprated THICK BORDER FOR BOTHs...plz HELP....

**royUK** · 05-31-2008, 08:32 AM

please edit your post with Code Tags.

You can start by removing all the lines like

Please Login or Register  to view this content.

**davesexcel** · 05-31-2008, 10:35 AM

I read your code, and tried to run it, and it turned out nothing like your formatted example, so I recorded a macro, cleaned it up a bit, and it looks something like this...

Please Login or Register  to view this content.

It looks like, your example, let me know if there is anything missing...

**Simon Lloyd** · 05-31-2008, 11:21 AM

Namz, please comply with the moderators request (RoyUk) when you have done so i will gladly undelete davesexcel's solution for you!

As per our rules, we feel we cannot answer a question until they are adhered to as this makes our job easier and helps you get the responses you need, everyone gets asked to do the same to keep our forum a great place to visit!

If you are experiencing trouble or need help then please PM myself or a moderator for help.

**Shijesh Kumar** · 05-31-2008, 04:15 PM

Hi,

If you are looking to just format the borders with outer thick lines and inner dotted line then this code will be sufficient.

After typing this code in VBA editor, return to excel.
Click on tools menu , then macors.
It will Open the macros window, here you click on option button.
In will open a small window called MACROS OPTION.
Here you can put shortkey to run marcos like
Ctrl + t ( so when u press control key and t macros will run).

To run macros select the range where u want the formatting and then click control key + T

Please Login or Register  to view this content.

If you need further assistance or modification in the macros you can PM me.

**VBA Noob** · 05-31-2008, 04:26 PM

Originally Posted by Shijesh Kumar

Hi,

If you are looking to just format the borders with outer thick lines and inner dotted line then this code will be sufficient.

After typing this code in VBA editor, return to excel.
Click on tools menu , then macors.
It will Open the macros window, here you click on option button.
In will open a small window called MACROS OPTION.
Here you can put shortkey to run marcos like
Ctrl + t ( so when u press control key and t macros will run).

To run macros select the range where u want the formatting and then click control key + T

Please Login or Register  to view this content.

If you need further assistance or modification in the macros you can PM me.

Slightly shorter

Please Login or Register  to view this content.

VBA Noob

**Shijesh Kumar** · 05-31-2008, 04:48 PM

Hey,

U r right... i was trying the same but i didnt know BorderAround feather...

Thanks buddy the increasing my excel VBA knowledge

namz use this because its better one

Please Login or Register  to view this content.

**namz** · 05-31-2008, 08:25 PM

Originally Posted by Simon Lloyd

Namz, please comply with the moderators request (RoyUk) when you have done so i will gladly undelete davesexcel's solution for you!

As per our rules, we feel we cannot answer a question until they are adhered to as this makes our job easier and helps you get the responses you need, everyone gets asked to do the same to keep our forum a great place to visit!

If you are experiencing trouble or need help then please PM myself or a moderator for help.

Hi Simon , just pasted my edited code...let me know what other changes can i do that wud make easier for experts on this forum to assist me..cheerz!!

**Simon Lloyd** · 06-01-2008, 01:54 AM

Namz you need to add code tags to your code please re post your code using code tags (highlight your code and press # when in the new post window) i will then un-delete the relevant posts!

**royUK** · 06-01-2008, 03:35 AM

Post locked due to namz ignoring the rules & not responding to moderator's requests.

**namz** · 06-01-2008, 04:30 AM

Hi Admin & Moderator, is this what i was supposed to do ?
Plz advise i am newbie to macros and to this forum so dont understand lot of things...

Please Login or Register  to view this content.

**namz** · 06-01-2008, 04:47 AM

This is what a macro code should do ; Hope this makes sense...thanks.

1. Finds numeric values in rows falling in colum B,C,D,E , than border those cells as dotted (i.e. right click on cell - format column - Border - Style - 3rs on top left row).

2. After filling cell border style as mentioned above , macro should do a THICK BOX BORDER around a cells falling above dotted cells.. i.e. one big sqaure outlined contaiing dotted cells with numeric values.

3. Trick part , my sheet is divided into 2 parts , INTERNAL and EXTERNAL. So i have writen "INTERNAL" in A1 cell and EXTERNAL in 'A' row in last row after INTERNAL lines. So the THICK BOX BORDER as requested in pt 2 shud be separted ones for INTERNAL and sep from EXTERNAL.

**royUK** · 06-01-2008, 04:53 AM

The code tags are what we requested. thanks for doing that.

I have edited the original post, restored the hidden answers & merged the threads.

**namz** · 06-06-2008, 07:38 PM

Originally Posted by royUK

The code tags are what we requested. thanks for doing that.

I have edited the original post, restored the hidden answers & merged the threads.

Hi roy, can i plz have some help my last reply on post " Copy\Paste multiple criteria based rows to new sheet "

**Simon Lloyd** · 06-07-2008, 03:59 AM

Namz, you haven't complied with my request for a post in the "General" forum agreeing to our rules which are in place to help everyone even you!

Please comply or i will have no option but to ban you, i really don't want to take this course of action as we try to keep these forums a happy friendly community.

Change cell border & outline in a macro

LinkBack

Thread Tools

Display