Format Cells by code
 

Hi All
The code below works fine, but I would like to simplify it.
As you can tell it is recorded code except for the cell ranges.
Any assistance in shortening the code would be appreciated, if only to
improve my knowledge !!!

With range("E17:G" & lrow + 2)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Font.Size = 10
.NumberFormat = "#,##0.00"
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
For Each c In range("H17:H" & lrow + 2)
With c.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Next
For Each c In range("C17:C" & lrow + 2)
With Selection
.Borders(xlEdgeLeft).LineStyle = xlNone
End With
Next


Regards
Michael M

Format Cells by code
 

Not much you can do about the borders, but is Selection the same range as
range("E17:G" & lrow + 2)?

And what is going on here

For Each c In range("C17:C" & lrow + 2)
With Selection
.Borders(xlEdgeLeft).LineStyle = xlNone
End With
Next

You extract c and then don't use it, but revert to selection. Should it be


For Each c In range("C17:C" & lrow + 2)
c.Borders(xlEdgeLeft).LineStyle = xlNone
Next


Maybe overall

With range("E17:G" & lrow + 2)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Font.Size = 10
.NumberFormat = "#,##0.00"
End With
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
For Each c In range("H17:H" & lrow + 2)
With c.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Next
For Each c In range("C17:C" & lrow + 2)
c.Borders(xlEdgeLeft).LineStyle = xlNone
Next



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Michael M" wrote in message
...
Hi All
The code below works fine, but I would like to simplify it.
As you can tell it is recorded code except for the cell ranges.
Any assistance in shortening the code would be appreciated, if only to
improve my knowledge !!!

With range("E17:G" & lrow + 2)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Font.Size = 10
.NumberFormat = "#,##0.00"
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
For Each c In range("H17:H" & lrow + 2)
With c.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Next
For Each c In range("C17:C" & lrow + 2)
With Selection
.Borders(xlEdgeLeft).LineStyle = xlNone
End With
Next


Regards
Michael M

Format Cells by code
 

Bob
Thanks for your response.
You are, of course, correct. This section is redundant ( For Each c In
range("C17:C" & lrow + 2), but I didn't pick it up.
The sheet I am working on had a number of merged cells which led to a
nightmare to try and code. The more I tried to fix it, the uglier it got.
BTW, it wasn't my sheet.....but I was asked to do something with it.

Regards
Michael M


"Bob Phillips" wrote:

Not much you can do about the borders, but is Selection the same range as
range("E17:G" & lrow + 2)?

And what is going on here

For Each c In range("C17:C" & lrow + 2)
With Selection
.Borders(xlEdgeLeft).LineStyle = xlNone
End With
Next

You extract c and then don't use it, but revert to selection. Should it be


For Each c In range("C17:C" & lrow + 2)
c.Borders(xlEdgeLeft).LineStyle = xlNone
Next


Maybe overall

With range("E17:G" & lrow + 2)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Font.Size = 10
.NumberFormat = "#,##0.00"
End With
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
For Each c In range("H17:H" & lrow + 2)
With c.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Next
For Each c In range("C17:C" & lrow + 2)
c.Borders(xlEdgeLeft).LineStyle = xlNone
Next



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Michael M" wrote in message
...
Hi All
The code below works fine, but I would like to simplify it.
As you can tell it is recorded code except for the cell ranges.
Any assistance in shortening the code would be appreciated, if only to
improve my knowledge !!!

With range("E17:G" & lrow + 2)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Font.Size = 10
.NumberFormat = "#,##0.00"
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
For Each c In range("H17:H" & lrow + 2)
With c.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Next
For Each c In range("C17:C" & lrow + 2)
With Selection
.Borders(xlEdgeLeft).LineStyle = xlNone
End With
Next


Regards
Michael M

Format Cells by code
 

Aah yes, merged cells, nightmare!

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Michael M" wrote in message
...
Bob
Thanks for your response.
You are, of course, correct. This section is redundant ( For Each c In
range("C17:C" & lrow + 2), but I didn't pick it up.
The sheet I am working on had a number of merged cells which led to a
nightmare to try and code. The more I tried to fix it, the uglier it got.
BTW, it wasn't my sheet.....but I was asked to do something with it.

Regards
Michael M


"Bob Phillips" wrote:

Not much you can do about the borders, but is Selection the same range as
range("E17:G" & lrow + 2)?

And what is going on here

For Each c In range("C17:C" & lrow + 2)
With Selection
.Borders(xlEdgeLeft).LineStyle = xlNone
End With
Next

You extract c and then don't use it, but revert to selection. Should it
be


For Each c In range("C17:C" & lrow + 2)
c.Borders(xlEdgeLeft).LineStyle = xlNone
Next


Maybe overall

With range("E17:G" & lrow + 2)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Font.Size = 10
.NumberFormat = "#,##0.00"
End With
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
For Each c In range("H17:H" & lrow + 2)
With c.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Next
For Each c In range("C17:C" & lrow + 2)
c.Borders(xlEdgeLeft).LineStyle = xlNone
Next



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Michael M" wrote in message
...
Hi All
The code below works fine, but I would like to simplify it.
As you can tell it is recorded code except for the cell ranges.
Any assistance in shortening the code would be appreciated, if only to
improve my knowledge !!!

With range("E17:G" & lrow + 2)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Font.Size = 10
.NumberFormat = "#,##0.00"
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
For Each c In range("H17:H" & lrow + 2)
With c.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Next
For Each c In range("C17:C" & lrow + 2)
With Selection
.Borders(xlEdgeLeft).LineStyle = xlNone
End With
Next


Regards
Michael M