change background color of row until condition is met

  • Thread starter Thread starter fullymooned
  • Start date Start date
F

fullymooned

I have 2 columns, status and order. If the status is "T", then the whole
row for that work order should have a yellow background. next time the
order is entered and its status is still "T", that row should be yellow
too until the status for the order becomes "C"
when it bcomes "C", all the yellow rows should be back to default

A B
ORDER STATUS
1 T
2 C
1 T
1 C

So the first and third rows should be yellow, but when i enter the
fourth row and enter "C" for status, all rows associated with order 1
should be back to default, ie no background color.

Thanks
 
Hi
see your answers in Excel.misc.

Frank
P.S.: please don't multipost as this scatters your answers
 
I think this should do it:

Sub test()
Dim i As Long, j As Long, lngLastRow As Long, lngColour As Variant

With Sheet1
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row

For i = lngLastRow To 2 Step -1
Select Case .Cells(i, 2).Value
Case "T": lngColour = 6
Case "C": lngColour = xlColorIndexNone
End Select
For j = i + 1 To lngLastRow
If .Cells(i, 1).Value = .Cells(j, 1).Value Then
lngColour = .Rows(j).Interior.ColorIndex
Exit For
End If
Next
.Rows(i).Interior.ColorIndex = lngColour
Next
End With
End Sub
 
Back
Top