VBA Code To Add A Border

Oct 4, 2011

The below code adds a line to the next blank row. I would like to add a border to this row that the values are going into. Columns "A:G"


Private Sub CMD_Add_Click()
Dim rNextCl As Range
Set rNextCl = Worksheets("Main").Cells(Rows.Count, 2).End(xlUp).Offset(2, 0)
Dim iRow As Long


View 9 Replies


VB Border Color Code

Nov 6, 2007

part of my code looks like this. Instead of it being if c.interior.colorindex i want it to be if top border color = 4.

How do i syntax this?

For Each c In Worksheets("Sheet1").Range("B7:DE7").Cells
If c.Interior.ColorIndex = 4 Then Range("DH7").Value = Range("DH7").Value + c.Value
Also, is there a way to specify Range("DH") and make the row be the current row, not hard coded to 7

View 9 Replies View Related

Code For Cell Border Styling

Jun 1, 2008

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.

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.

View 14 Replies View Related

VBA Code To Create Border Around Used Cells In Worksheet

Nov 20, 2013

I'm writing/recording a macro and would like some VBA code. I'm trying to make a border around all used cells in my sheet. When I use the macro there will be a variable number of used cells.

I'd like code that says:

If cell A2 has text then format cells A2:CL2 with an outline border Repeat for each row in the sheet until there there is no more text in column A.

Also, not sure if it matters, but I'm working on a mac.

View 9 Replies View Related

Code Adjustment To Copy And Paste Bold Text And Border?

Aug 13, 2014

Is it possible to modify the attached code so that it will copy bold text and border as shown in attachment sample1 and paste in sheet Shop. Currently the code just copy's and pastes without bold text and borders.


View 4 Replies View Related

VBA To Put Border Around A Range

Mar 15, 2013

I am after a bit of code which find a value in a cell "Grand Total" it may not be a the last row

Then finds the last column in the row and puts a border around the whole range.

View 4 Replies View Related

Border Around Duplicates

Jan 29, 2009

I have a list (approx 2000 rows). I'd like to put a seperate border around any duplicates that are in the fourth column. If there isn't a duplicate then the regular gridlines are fine. I'd only like the first four columns to include this border. Is there a way for excel to automatically place borders around duplicates? Here is an example of what I have and what I would like it to look like.

View 9 Replies View Related

VBA To Know What Kind Of Border It Is

Mar 28, 2009

Does any know of excel code to know what know what kind of border it is. I converted a pdf file and now the data are on excel and there are borders and it looks to me like it is border with thickness of medium but when I run a code I produced and mark X to the column next to it to show that it is that border it gave me X on all the rows and so the medium thick border is excel grid without any borders.

View 9 Replies View Related

Border Around Cells With Same Value

Mar 23, 2006

Below a small example with the borders as they should be. How do i get a macro to put the borders automatically, depending on the similarity of the cells in column B ? Note the blanc column with the borders should be made automatically as well. Second problem: the minimum height of a "border" should be 34pixels as in the example so you can write something in there after printing the sheet.

View 9 Replies View Related

Add Border Around Selection

Aug 24, 2007

There was also another bit of code which added a border around the selected text.
It got stuck on this:

With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

The problem was that there was no Inside Horizontal if there was only one row. I had to chage my code to include a header as well as any selected rows - giving me the two row minimum required. This does the job for now - I'll have to give it some thought on how to get around the problem without the extra header though.

View 4 Replies View Related

Cell Border Non-visible?

Jan 7, 2009

I have seen spreadsheet where the user has made the outline of the cell border non-visible. This seems like a fairly simple task, but I can't seem to find out how you format this type of border.

View 2 Replies View Related

Xl 07 Can't Change Border Colour

Jan 16, 2009

I am using Excel 2007. One of my worksheets has a dark border around some cells. Instead of being blue the lines are black. I used the right click and format cells option but it does not work.

View 10 Replies View Related

Border In Each Page Break

Jun 9, 2009

Bottom border each page,

View 9 Replies View Related

Cell Border Disappearance

Dec 19, 2009

When I enter data (digits) into a cell, its left border
becomes thinner ! Where is the connection between format and content ?

View 14 Replies View Related

Border Formatting In Excel

Nov 3, 2013

Creating border in different size at single attempt. Example as shown in the picture.

Excel Format.jpg

View 1 Replies View Related

How To Change Type Of Border

Apr 24, 2014

How can I modify this code:

HTML Code: 

Sub aaanewa()
Dim lRow As Long, c As Range
lRow = Cells(Rows.Count, "D").End(xlUp).Row
For Each c In Range("D1:D" & lRow)
If c.Value > "" Then


To include this:

HTML Code: 

With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -0.14996795556505
.Weight = xlThin
End With

so i can change the color and size of border

View 2 Replies View Related

Removing Border Of Group Box

Nov 20, 2008

I've used Group Boxes to isolate some Option Buttons. I would like to remove the line border of the Group Box, but I don't see a way to format it.

View 2 Replies View Related

Using VBA To Add Border To Bottom Of Certain Cells

Jul 19, 2012

I need to underline certain cells based on the value of a cell in this case from column "E". Right now I run a macro to look at the value in each cell in column "E". THe macro then adds a certain amount of blank rows below the that row. So, if cell E1 has a 4, my macro runs a calculation that says 4 divided by 3, +2. So it would add 3 blank rows under row 1. It does this all the way down my column of data.

Now it gets tricky. To the right of column E, I have 6 blank columns, F,G,H,I,J,K. F,H, and J are spacer rows with no data. I need them blank with out any borders. Columns G, I, and K are the columns that I am wanting to add borders on the bottom of the cells based on the value in column E.

I need code to look at the value in E1, in this case 4, and add borders to 4 cells, in this case G1, I1, and K1 and then G2. If the value would have been 5, I need I2, 6 would add a border to K2 and 7 would add a border to G3. Catch my drift?

So, every cell in column E will not have a value because of me adding blank rows. So when there is a value, I need to add borders starting on the same row the value is in. At most, there will only be 3 underlined cells per row because of the format. Once the top row is filled, I need to drop down to the very next row.

View 9 Replies View Related

Draw A Border Using Line?

Aug 14, 2013

I'm trying to use VBA to draw a border using the drawing toolbar and using the line. i need it to draw a border around a select area i hightlight.

i have attached a example below, but not sure if it will appear.

View 1 Replies View Related

VBA / Special Border Cell

Oct 11, 2013

I am trying have the macro go from H7:H300 and if the value in the cell is "Grand" the I need it to border the top, right, and bottom. Not the left side and then keep going until all cells are checked.

View 1 Replies View Related

BORDER Around Active Cell

Dec 4, 2006

I have a spreadsheet with DDE formulas in the cells. When the contents of any cell changes I want to DRAW A BORDER (NOT CHANGE THE BACKGROUND COLOR !!) AROUND THE CELL .. When another cell's value is updated I want to REMOVE the border which was around the"OLD" cell and now place a border around the most recently changed cell, etc.

Only the newest active cell should have a border around it !!!

View 9 Replies View Related

Insert Border When Data Changes

Mar 5, 2008

What I'm really looking for is to box in data as the data changes using the border feature.

Column A would contain the data to look at however there may be additional data in columns B, C, D and so on that would need to be "boxed in" with the group of like data.

I can't get the HTML to work so we'll have to go sans spreadsheet on this one.

View 9 Replies View Related

Conditional Format (Border)

Sep 18, 2008

I'm trying to conditional format a cell that contains a formula that when the formula returns anything other than "" the cell contains a border.
My formula within the cell is referencing another worksheet

Ideally the formula within the conditional format would be
but it isn't possible to reference another worksheet within a conditional format.

View 9 Replies View Related

Page Border Like Word

May 12, 2009

Is there any chance to draw page borders in excel like ms word
i have huge data around 450 pages for which i want to set page border
as per my knowledge i have to select each page and set the border
which is taking lot of time.
instead of this is there any option to set page border

View 9 Replies View Related

Vba, Separating Ranges With A Border

Feb 7, 2007

I have a spreadsheet of data, and all entries are grouped by different unique reference (three digit number). I need something in VBA so if the the unique reference in the columns are the same, to place a border over the ammending data. The actaull; spreadsheet is sorted ascending by the unique references so all the entries are already grouped together. EG - So if I ran the code it would put a border around the group of rows for unique reference 126.

Unique reference

I attempted using if activecell offset, but i dont think the logic works. I ran a macro for the border, it is below.

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With.........................

View 7 Replies View Related

Find Border Using Xldiagonaldown

Feb 21, 2007

Is it possible to use a FindFormat to find a Border(xlDiagonalDown)?

If so can you please show me the code to do so.

Sub Find_F()

Dim e As Range
Dim r As Range
Dim c As Range
Dim lCount As Long
Dim strFirstAddress As String

Set e = Range("A1:A6")

With Application.FindFormat.Font
.Bold = True
.Italic = True
.Size = 16
End With

View 4 Replies View Related

Border Last Range Of Cells

Aug 3, 2008

I am trying to have a border drawn around a group of cells. This group of cells changes per list I make, but only the height changes. The width is always the same (from column A to column J) and the top is always at row 5. Only the last row changes. How can a border be drawn around this group of cells no matter the size of the group of cells? Below is the macro I have written so far.

ActiveWindow.SmallScroll ToRight:=7
ActiveWindow.SmallScroll ToRight:=-7
Rows("2:2").RowHeight = 49.5
lastrow1 = Range("A65536").End(xlUp).Select
Range("A5:J" & lastrow1).BorderAround.Weight = xlThin
lastrow2 = Cells(Cells.Rows.Count, "C").End(xlUp).Row
For I = lastrow2 To 2 Step -1..........................

View 2 Replies View Related

Macro To Remove Picture Border Or Set It To None

Aug 13, 2012

I have a picture that has been inserted into a worksheet via a macro and I have a number of macros to increase and decrease size. They work fine.

However, whenever I run one of those macros by selecting a button on the worksheet, it results in a border around the picture.

- worksheet is called "Admin"
- picture is called "ClientLogo"
- Line.Weight set to "0" or "False" or "xlNone" or removed all result in a border

Question: Code / syntax for removing or setting the border to nothing?


Sub ReduceShape()
Dim shp As ShapeRange
On Error Goto NoSelectionMade
Set shp = Selection.ShapeRange


View 2 Replies View Related

Creating Border For A Range Using R1C1

Mar 6, 2013

I'm trying to create a border for a range using R1C1 and get an error.

VB: Sheet11.Range(Cells(y, 1), Cells(y, 5)).BorderAround Weight:=xlThin

If I do not use R1C1 it works...

VB: Sheet11.Range("A18:E18").BorderAround Weight:=xlThin

View 2 Replies View Related

Conditional Formatting Border Formula?

Mar 16, 2014

I have a list that, how do i create a border that data contains formula (formula not display/blank)...

i want to create dynamic border with condition if data display/showing must be border shown too...

for this, i want to line "TOTAL" in column AD expands up, based on data display/showing...

note : in cell AF2 as parameter/controller to show data by month (type 2 instead February or 3 --March etc...)

View 5 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved