Format LastRow style

Dave Ashcroft

New Member
Joined
May 24, 2008
Messages
24
Hi I have received loads of help from the forum for which I am eternally grateful, I now have yet another question. :confused:

The code listed below enters new data into my workbook on the sheet selected by the ComboBox1.Value, this all works great. I now require to format the last row before the the sheet does the sort by column “B”. I have recorded a macro of the desired formats indicated below cmdNew but am at a loss of how to assign it to the last row, has anyone any ideas.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
Private Sub cmdNew_Click()
With ThisWorkbook.Worksheets(Me.ComboBox1.Value)
LastRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'check for Name
If Trim(Me.txtName.Value) = "" Then
Me.txtName.SetFocus
MsgBox "Please enter Employee Name"
Exit Sub
End If
'check for Badge Number
If Trim(Me.txtBadge.Value) = "" Then
Me.txtBadge.SetFocus
MsgBox "Please enter Employee Badge#"
Exit Sub
End If
'check for Grade Code
If Trim(Me.txtGC.Value) = "" Then
Me.txtGC.SetFocus
MsgBox "Please enter Employee Grade Code"
Exit Sub
End If
'check for Months in IDP
If Trim(Me.txtMon.Value) = "" Then
Me.txtMon.SetFocus
MsgBox "Please enter Number of Months Must be greater than 1"
Exit Sub
End If
'check for Months in IDP
If Trim(Me.txtTas.Value) = "" Then
Me.txtTas.SetFocus
MsgBox "Please enter Number of Tasks Requested This Month"
Exit Sub
End If
'check for Months in IDP
If Trim(Me.txtEva.Value) = "" Then
Me.txtEva.SetFocus
MsgBox "Please enter Number of Tasks Evaluated This Month"
Exit Sub
End If
'check for Months in IDP
If Trim(Me.txtTot.Value) = "" Then
Me.txtTot.SetFocus
MsgBox "Please enter Total Tasks Completed"
Exit Sub
End If
'check for Months in IDP
If Trim(Me.txtJob.Value) = "" Then
Me.txtJob.SetFocus
MsgBox "Please enter Employees Job"
Exit Sub
End If
'copy the data to the database
.Cells(LastRow, 1).Value = Me.txtName.Value
.Cells(LastRow, 2).Value = Me.txtBadge.Value
.Cells(LastRow, 3).Value = Me.txtGC.Value
.Cells(LastRow, 4).Value = Me.txtMon.Value
.Cells(LastRow, 6).Value = Me.txtTas.Value
.Cells(LastRow, 7).Value = Me.txtEva.Value
.Cells(LastRow, 11).Value = Me.txtTot.Value
.Cells(LastRow, 9).Value = Me.txtJob.Value
End With
'clear the data
Me.txtName.Value = ""
Me.txtBadge.Value = ""
Me.txtGC.Value = ""
Me.txtMon.Value = ""
Me.txtTas.Value = ""
Me.txtEva.Value = ""
Me.txtTot.Value = ""
Me.txtJob.Value = ""
Me.txtBadge.SetFocus
New Macro to “format”<o:p></o:p>
Range("A4:L50").Select
ActiveWorkbook.Worksheets(Me.ComboBox1.Value).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(Me.ComboBox1.Value).Sort.SortFields.Add Key:=Range( _
"B4:B50"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(Me.ComboBox1.Value).Sort
.SetRange Range("A4:L50")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
<o:p> </o:p>
End Sub
<o:p> </o:p>
Private Sub Format()
Range("A15:L15").Select
With Selection.Font
.Name = "Arial"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 12611584
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A15").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B15:L15").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
Here's the call:

Code:
    Dim rng As Range
    Set rng = Range(Cells(lastrow, 1), Cells(lastrow, 12))
    FormatRow rng

And then something like this, I believe...
Code:
Sub FormatRow(rng As Range)
With rng
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlCenter
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
End With
With rng.Font
    .Bold = True
    .Name = "Arial"
    .Size = 9
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
End With
With rng.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With rng.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With rng.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With rng.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With rng.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With rng.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With rng.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 12611584
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
Set subrng = Cells(rng.Row, 1)  'Cell in Col A
With subrng
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Set subrng = Range(Cells(rng.Row, 2), Cells(rng.Row, 12)) 'Cells in Col B to L
With subrng
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
End Sub
 

Dave Ashcroft

New Member
Joined
May 24, 2008
Messages
24
Thanks for the quick response, I'm still learning and have been stumped by this for days now.
The call and the format code work great but unfortunately the format appears on the active worksheet rather than the sheet defined by Me.ComboBox1.value.
 

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
Ok...

we can change the call to be more specific

Code:
    Dim ws As Worksheet
    Dim rng As Range
    Set ws = Sheets(Me.ComboBox1.Value)
    Set rng = ws.Range(ws.Cells(lastrow, 1), ws.Cells(lastrow, 12))
    FormatRow rng
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,399
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top