change background color of row until condition is met
 

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


---
Message posted from http://www.ExcelForum.com/

change background color of row until condition is met
 

Hi
see your answers in Excel.misc.

Frank
P.S.: please don't multipost as this scatters your answers


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


---
Message posted from http://www.ExcelForum.com/

change background color of row until condition is met
 

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



--
Rob van Gelder - http://www.vangelder.co.nz/excel


"fullymooned " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/