Formatting stop when reach blank cell

[Alberta Rose]

I'm still having issues with this project. The coding below will not format
to row 450, it stops at row 82. Any ideas?

'add column for Default
Columns("u:u").Select
Selection.Insert Shift:=xlToRight
Selection.ColumnWidth = 7
Columns("u:u").Select
Selection.Interior.ColorIndex = xlNone
Range("u5").Select
ActiveCell.FormulaR1C1 = "Default"
With ActiveCell.Characters(Start:=1, Length:=7).Font
..Name = "Arial"
..FontStyle = "Bold"
..Size = 8
..Strikethrough = False
..Superscript = False
..Subscript = False
..OutlineFont = False
..Shadow = False
..Underline = xlUnderlineStyleNone
..ColorIndex = xlAutomatic
End With
Range("u5").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
..LineStyle = xlContinuous
..Weight = xlMedium
..ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
..LineStyle = xlContinuous
..Weight = xlMedium
..ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
..LineStyle = xlContinuous
..Weight = xlHairline
..ColorIndex = xlAutomatic
End With
Range("u5:u450").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
..LineStyle = xlContinuous
..Weight = xlMedium
..ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
..LineStyle = xlContinuous
..Weight = xlMedium
..ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
..LineStyle = xlContinuous
..Weight = xlHairline
..ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
..LineStyle = xlContinuous
..Weight = xlHairline
..ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
..LineStyle = xlContinuous
..Weight = xlHairline
..ColorIndex = xlAutomatic
End With
Range("v450").Select

Also, where would I insert the coding to have the formatting stop when the
cell in Column E is blank? Say if there were 90 rows on one sheet and the
next had 300, I want the formatting to stop when there is nothing in Column
E.

Thanks :0) Laurie

 

[Alberta Rose]

I have two issues. Firstly, in my spreadsheet I have a column E which
contains cost types. In column U I have formulas that depending on the value
in column E assigns a particular formula in column U. I have also formatted
column U with borders, etc. but the formatting continues even though there's
nothing in column E. I have multiple spreadsheets depending on the contract,
some have 80 rows, some have over 300. I need the macro to be able to run no
matter how many rows it has. I want to stop the formatting in column U when
there is a blank in column E.

 

[Alberta Rose]

I have two issues. This one pertains to why my formatting (borders, etc)
won't continue to row 450 like I've instructed it to. The other columns in
this spreadsheet that I've added and put row 450 in the coding are stopping
at row 450. Column U just does the formatting to row 82, even though there
is more below that.

Second issue. I get 30 spreadsheets every month for this macro to run on.
Sometimes there are 60 rows, others have over 300. Right now the formatting
(borders, etc.) continue below where I want them to. I want the formatting
to stop in Column Z for example when column E is blank.

Laurie

 

[Patrick Molloy]

but the code you posted inserts a new column U - there are no formula

 

[Joel]

The code you provided only formats column U. I made changes including only
going to the last row. The only reason this code shouldn't work is if the
wrong worksheet is the active sheet.


Sub test()


With ActiveSheet
With .Columns("U")
'add column for Default
.Insert
.ColumnWidth = 7
.Interior.ColorIndex = xlNone
End With

With .Range("u5")
.Value = "Default"
With .Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
.Borders(xlEdgeTop).LineStyle = xlNone
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
End With

LastRow = .Range("U" & Rows.Count).End(xlUp).Row

With .Range("u5:u" & LastRow)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone

With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With

With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With

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

End Sub

 

[Alberta Rose]

The formulas are in a different part of the macro. This just adds the new
column and formats it.

 

[Alberta Rose]

I get an error on the last line of .LineStyle = xlContinuous. Also, there
are multiple End With's in the code. I'm not familiar with this...

Laurie