Solved: Row Color Range When Cell Changes Color

[sooty8]

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

[Bob Phillips]

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.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#)
c.Resize(, 7).Interior.ColorIndex = 35
'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

[sooty8]

Hi Xld

As usual expert advice spot on -- just one query is it possible to also color the cells previous that would be cells for example if the selected row was 23 -- cells A23 & B23 as the others would be already colored

Many thanks

Sooty 8

[Bob Phillips]

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.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#)
c.Offset(, -2).Resize(, 9).Interior.ColorIndex = 35
'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

[sooty8]

Hi Xld

Don't know how you remember all the right code and syntax -- once again many thanks for your help -- this morning I'm once again flavour of the month with my gaffer (pay rise nah!!) not that much flavour -- at least he can now easily see which customer hasn't paid their current invoice.

Regards

Sooty8