sooty8
06-22-2009, 11:56 AM
Hi
Column C is an ID number which can be in a range C9 - C500 and each one is different the code below finds the ID and then I can enter values in the textboxes and on clicking Add1 it fills the row with the values and changes the color of the cell for the found ID number how would I alter the code so it fills the row range A:J with the same color as the found cell, been trying different things for ages and getting nowhere fast -- so I'm asking the experts to once again help out.
Private Sub Add1_Click()
Dim c As Range, Cntrl As Control
On Error Resume Next
Set c = Columns(3).Find(Tb1B, LookIn:=xlValues, LookAt:=xlWhole)
c.Interior.ColorIndex = 35
c.Offset(, 1) = c.Offset(, 1) + (Tb2A * 1#)
c.Offset(, 2) = c.Offset(, 2) + (Tb3A * 2#)
c.Offset(, 3) = c.Offset(, 3) + (Tb4A * 3#)
c.Offset(, 4) = c.Offset(, 4) + (Tb5A * 4#)
c.Offset(, 5) = c.Offset(, 5) + (Tb6A * 5#)
c.Offset(, 6) = c.Offset(, 6) + (Tb7A * 1#)
'Clear Data
For Each Cntrl In Me.Controls
If Left(Cntrl.Name, 2) = "Tb" Or Left(Cntrl.Name, 3) = "Tb1B" Then
Cntrl.Value = ""
End If
Next
End Sub
Many Thanks
Sooty8
Column C is an ID number which can be in a range C9 - C500 and each one is different the code below finds the ID and then I can enter values in the textboxes and on clicking Add1 it fills the row with the values and changes the color of the cell for the found ID number how would I alter the code so it fills the row range A:J with the same color as the found cell, been trying different things for ages and getting nowhere fast -- so I'm asking the experts to once again help out.
Private Sub Add1_Click()
Dim c As Range, Cntrl As Control
On Error Resume Next
Set c = Columns(3).Find(Tb1B, LookIn:=xlValues, LookAt:=xlWhole)
c.Interior.ColorIndex = 35
c.Offset(, 1) = c.Offset(, 1) + (Tb2A * 1#)
c.Offset(, 2) = c.Offset(, 2) + (Tb3A * 2#)
c.Offset(, 3) = c.Offset(, 3) + (Tb4A * 3#)
c.Offset(, 4) = c.Offset(, 4) + (Tb5A * 4#)
c.Offset(, 5) = c.Offset(, 5) + (Tb6A * 5#)
c.Offset(, 6) = c.Offset(, 6) + (Tb7A * 1#)
'Clear Data
For Each Cntrl In Me.Controls
If Left(Cntrl.Name, 2) = "Tb" Or Left(Cntrl.Name, 3) = "Tb1B" Then
Cntrl.Value = ""
End If
Next
End Sub
Many Thanks
Sooty8