sooty8
02-24-2009, 09:50 AM
Hi All
The code below works exactly as required except I need the cells in Column A on the same Row to change colour when Tb1B.value enters the Cell on the Sheet. The info entered in Tb1B is alphanumeric e.g. ZB97X12345 is it possible to code it within the sub below so that the Cell changes to the colour Light Green.
Private Sub Add100_Click()
Dim c As Range, Cntrl As Control
On Error Resume Next
Set c = Columns(1).Find(cbo71, LookIn:=xlValues, lookat:=xlWhole)
c.Offset(, 2) = c.Offset(, 2) + (Tb1B.Value)
c.Offset(, 3) = c.Offset(, 3) + (Tb2A * 1#)
c.Offset(, 4) = c.Offset(, 4) + (Tb3A * 2#)
c.Offset(, 5) = c.Offset(, 5) + (Tb4A * 3#)
c.Offset(, 6) = c.Offset(, 6) + (Tb5A * 5#)
c.Offset(, 7) = c.Offset(, 7) + (Tb6A * 10#)
c.Offset(, 8) = c.Offset(, 8) + (Tb7A * 15#)
c.Offset(, 9) = c.Offset(, 9) + (Tb8A * 20#)
c.Offset(, 10) = c.Offset(, 10) + (Tb9A * 2#)
c.Offset(, 11) = c.Offset(, 11) + (Tb10A * 5#)
'Clear Data
For Each Cntrl In Me.Controls
If Left(Cntrl.Name, 2) = "Tb" Or Left(Cntrl.Name, 3) = "cbo" Then
Cntrl.Value = ""
End If
Next
cbo71.SetFocus
End Sub
Many Thanks
Sooty8
The code below works exactly as required except I need the cells in Column A on the same Row to change colour when Tb1B.value enters the Cell on the Sheet. The info entered in Tb1B is alphanumeric e.g. ZB97X12345 is it possible to code it within the sub below so that the Cell changes to the colour Light Green.
Private Sub Add100_Click()
Dim c As Range, Cntrl As Control
On Error Resume Next
Set c = Columns(1).Find(cbo71, LookIn:=xlValues, lookat:=xlWhole)
c.Offset(, 2) = c.Offset(, 2) + (Tb1B.Value)
c.Offset(, 3) = c.Offset(, 3) + (Tb2A * 1#)
c.Offset(, 4) = c.Offset(, 4) + (Tb3A * 2#)
c.Offset(, 5) = c.Offset(, 5) + (Tb4A * 3#)
c.Offset(, 6) = c.Offset(, 6) + (Tb5A * 5#)
c.Offset(, 7) = c.Offset(, 7) + (Tb6A * 10#)
c.Offset(, 8) = c.Offset(, 8) + (Tb7A * 15#)
c.Offset(, 9) = c.Offset(, 9) + (Tb8A * 20#)
c.Offset(, 10) = c.Offset(, 10) + (Tb9A * 2#)
c.Offset(, 11) = c.Offset(, 11) + (Tb10A * 5#)
'Clear Data
For Each Cntrl In Me.Controls
If Left(Cntrl.Name, 2) = "Tb" Or Left(Cntrl.Name, 3) = "cbo" Then
Cntrl.Value = ""
End If
Next
cbo71.SetFocus
End Sub
Many Thanks
Sooty8