DrPepper
06-30-2009, 10:09 AM
Help!!! I have a row highlighting issue.
I have two worksheet, "Sign-in" and "Data". On Sign-in, I have a button created that launches a userform. This userform will put the information the user enters, into the Data worksheet. Value 7 is me.NewPet.Value, which is a checkbox, if the pet is new.
If the pet is new I need the whole row, in the Data worksheet, to be the color Red.
My problem is: I can't use activecell, since I'm searching for the first empty cell. How can I color the row within the "first empty row" search?
Private Sub AddPet_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.Date1.Value
ws.Cells(iRow, 2).Value = Me.PetName.Value
ws.Cells(iRow, 3).Value = Me.OwnerName.Value
ws.Cells(iRow, 4).Value = Me.FromDate.Value
ws.Cells(iRow, 5).Value = Me.ToDate.Value
ws.Cells(iRow, 6).Value = Me.Phone.Value
ws.Cells(iRow, 7).Value = Me.NewPet.Value
ws.Cells(iRow, 8).Value = Me.Shots_Need.Value
ws.Cells(iRow, 9).Value = Me.Comments.Value
'color entire row Red if "Yes" is checked for NewPet.
If Me.NewPet.Value = True Then
With ActiveCell.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Else
ws.Cells(iRow, 7).Value = ""
End If
'color entire row Yellow if "Needed" is checked for Shots_Need
If Me.Shots_Need.Value = True Then
With ActiveCell.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Else
ws.Cells(iRow, 8).Value = "Up To Date"
End If
'clear the data
Me.Date1.Value = ""
Me.PetName.Value = ""
Me.OwnerName.Value = ""
Me.FromDate.Value = ""
Me.ToDate.Value = ""
Me.Phone.Value = ""
Me.NewPet.Value = ""
Me.Shots_Need.Value = ""
Me.Comments.Value = ""
Me.Date1.SetFocus
End Sub
Thanks in advance for the help.
Dr Pepper
I have two worksheet, "Sign-in" and "Data". On Sign-in, I have a button created that launches a userform. This userform will put the information the user enters, into the Data worksheet. Value 7 is me.NewPet.Value, which is a checkbox, if the pet is new.
If the pet is new I need the whole row, in the Data worksheet, to be the color Red.
My problem is: I can't use activecell, since I'm searching for the first empty cell. How can I color the row within the "first empty row" search?
Private Sub AddPet_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.Date1.Value
ws.Cells(iRow, 2).Value = Me.PetName.Value
ws.Cells(iRow, 3).Value = Me.OwnerName.Value
ws.Cells(iRow, 4).Value = Me.FromDate.Value
ws.Cells(iRow, 5).Value = Me.ToDate.Value
ws.Cells(iRow, 6).Value = Me.Phone.Value
ws.Cells(iRow, 7).Value = Me.NewPet.Value
ws.Cells(iRow, 8).Value = Me.Shots_Need.Value
ws.Cells(iRow, 9).Value = Me.Comments.Value
'color entire row Red if "Yes" is checked for NewPet.
If Me.NewPet.Value = True Then
With ActiveCell.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Else
ws.Cells(iRow, 7).Value = ""
End If
'color entire row Yellow if "Needed" is checked for Shots_Need
If Me.Shots_Need.Value = True Then
With ActiveCell.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Else
ws.Cells(iRow, 8).Value = "Up To Date"
End If
'clear the data
Me.Date1.Value = ""
Me.PetName.Value = ""
Me.OwnerName.Value = ""
Me.FromDate.Value = ""
Me.ToDate.Value = ""
Me.Phone.Value = ""
Me.NewPet.Value = ""
Me.Shots_Need.Value = ""
Me.Comments.Value = ""
Me.Date1.SetFocus
End Sub
Thanks in advance for the help.
Dr Pepper