Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting a row of Cells
I have written a Macro to record Registration information for up to 2,000
people registered at a Lions Convention. I use a userform to enter the information for each Registration. Based on the type of Registration, I would like to be able to highlight the LastRow in an appropriate color. Since the database is dynamic, I am having difficulty selecting the Last row (Uses A(lastRow):l(Lastrow)). This is the code I have written (albeit not well) so far. It will highlight the very first cell in the LastRow but will not select entire row. If anyone can steer me straight I would appreciate it. My Code ================================= Private Sub CommandButton1_Click() Dim LastRow As Object Dim Response As String Dim Ts As Integer, Lu As Integer, Sheets2 As Worksheet ' Application.Visible = False Set LastRow = Sheet1.Range("a65536").End(xlUp) Ts = 0 Lu = 0 If Sheets.Count < 1 Then Sheets(1).Activate LastRow.Activate LastRow.Offset(1, 0).Value = txtName.Text LastRow.Offset(1, 1).Value = txtClub.Text LastRow.Offset(1, 2).Value = cmbDist.Text LastRow.Offset(1, 3).Value = 1 ' txtPersons.Text On Error Resume Next If CheckBox1 = True Or CheckBox2 = True Then LastRow.Offset(1, 4).Value = 10 Else LastRow.Offset(1, 4).Value = 20 End If If CheckBox1 = True Then MsgBox ("Sun Only - No Room deposit required!") LastRow.Offset(1, 5).Value = "" End If If CheckBox2 = True Then MsgBox ("This is a Leo - Registration is only One Half!") Else LastRow.Offset(1, 5).Value = txtRoomDep.Text End If LastRow.Offset(1, 8).Value = txtLunch.Text Lu = LastRow.Offset(1, 8).Value * 25 Ts = Ts + Lu LastRow.Offset(1, 9).Value = txtTheatre.Text Lu = LastRow.Offset(1, 9).Value * 30 Ts = Ts + Lu LastRow.Offset(1, 10).Value = txtBanquet.Text Lu = LastRow.Offset(1, 10).Value * 55 Ts = Ts + Lu LastRow.Offset(1, 11).Value = txtDance.Text Lu = LastRow.Offset(1, 11).Value * 5 Ts = Ts + Lu ' LastRow.Offset(1, 12).Value = txtPDG.Text ' Lu = LastRow.Offset(1, 12).Value * 20 ' Ts = Ts + Lu LastRow.Offset(1, 6).Value = Ts If OptionButton1 = True Then LastRow.Offset(1, 14).Value = "M" ' Using Master Card End If If OptionButton2 = True Then LastRow.Offset(1, 14).Value = "V" ' Using Visa End If Set LastRow = Sheet1.Range("a65536").End(xlUp) LastRow.Activate MsgBox ("The Last row booked is " & LastRow()) ' Sheets(1).Activate Range.Cells("A(LastRow):L(LastRow)").Select With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 ' .Strikethrough = False ' .Superscript = False ' .Subscript = False ' .OutlineFont = False ' .Shadow = False ' .Underline = xlUnderlineStyleNone .ColorIndex = 10 End With MsgBox "One record written to Sheet1" ================================================== === DennisB |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting a row of Cells
Try
Range.Cells("A" & LastRow & ":L" & LastRow).Select -- HTH RP (remove nothere from the email address if mailing direct) "DennisB" wrote in message .. . I have written a Macro to record Registration information for up to 2,000 people registered at a Lions Convention. I use a userform to enter the information for each Registration. Based on the type of Registration, I would like to be able to highlight the LastRow in an appropriate color. Since the database is dynamic, I am having difficulty selecting the Last row (Uses A(lastRow):l(Lastrow)). This is the code I have written (albeit not well) so far. It will highlight the very first cell in the LastRow but will not select entire row. If anyone can steer me straight I would appreciate it. My Code ================================= Private Sub CommandButton1_Click() Dim LastRow As Object Dim Response As String Dim Ts As Integer, Lu As Integer, Sheets2 As Worksheet ' Application.Visible = False Set LastRow = Sheet1.Range("a65536").End(xlUp) Ts = 0 Lu = 0 If Sheets.Count < 1 Then Sheets(1).Activate LastRow.Activate LastRow.Offset(1, 0).Value = txtName.Text LastRow.Offset(1, 1).Value = txtClub.Text LastRow.Offset(1, 2).Value = cmbDist.Text LastRow.Offset(1, 3).Value = 1 ' txtPersons.Text On Error Resume Next If CheckBox1 = True Or CheckBox2 = True Then LastRow.Offset(1, 4).Value = 10 Else LastRow.Offset(1, 4).Value = 20 End If If CheckBox1 = True Then MsgBox ("Sun Only - No Room deposit required!") LastRow.Offset(1, 5).Value = "" End If If CheckBox2 = True Then MsgBox ("This is a Leo - Registration is only One Half!") Else LastRow.Offset(1, 5).Value = txtRoomDep.Text End If LastRow.Offset(1, 8).Value = txtLunch.Text Lu = LastRow.Offset(1, 8).Value * 25 Ts = Ts + Lu LastRow.Offset(1, 9).Value = txtTheatre.Text Lu = LastRow.Offset(1, 9).Value * 30 Ts = Ts + Lu LastRow.Offset(1, 10).Value = txtBanquet.Text Lu = LastRow.Offset(1, 10).Value * 55 Ts = Ts + Lu LastRow.Offset(1, 11).Value = txtDance.Text Lu = LastRow.Offset(1, 11).Value * 5 Ts = Ts + Lu ' LastRow.Offset(1, 12).Value = txtPDG.Text ' Lu = LastRow.Offset(1, 12).Value * 20 ' Ts = Ts + Lu LastRow.Offset(1, 6).Value = Ts If OptionButton1 = True Then LastRow.Offset(1, 14).Value = "M" ' Using Master Card End If If OptionButton2 = True Then LastRow.Offset(1, 14).Value = "V" ' Using Visa End If Set LastRow = Sheet1.Range("a65536").End(xlUp) LastRow.Activate MsgBox ("The Last row booked is " & LastRow()) ' Sheets(1).Activate Range.Cells("A(LastRow):L(LastRow)").Select With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 ' .Strikethrough = False ' .Superscript = False ' .Subscript = False ' .OutlineFont = False ' .Shadow = False ' .Underline = xlUnderlineStyleNone .ColorIndex = 10 End With MsgBox "One record written to Sheet1" ================================================== === DennisB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting cells | Excel Discussion (Misc queries) | |||
condionally formatting based on another cells formatting? | Excel Discussion (Misc queries) | |||
conditionally formatting cells based on other cells | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional Formatting Multiple cells based on 2 cells | Excel Worksheet Functions |