Assign a particular colour to rows based on their current status

[Co-op Bank]

Please help me! I have a spreadsheet which has two sheets

The 1st, ('Sales') which lists all the sales, each row details the sales
reference number, product and amount. It's constantly growing and is
currently about 300 rows deep.

The 2nd ('Status') updates the current status of each sale. This sheet has 4
columns, 'Sales Ref', 'Status', 'Status Version', and a 'Date' column which
logs the date the status was updated. The column 'Status Version' is the
number of times the version of each 'Sales Ref' has been updated and each
update is added to the list. 'Status' is the current position with the sale,
there are 6 possible positions. For example 'Forms out', 'Forms returned',
'Sale confirmed', 'Cancelled'.

What I really, really need is when I update the status of a sale on sheet
'Status' there is a VB script that will loop through my 'Sales' sheet and
highlight the row in a colour that is relevent to that newly updated status.

For example when I update a 'Sales Ref' on sheet 'Status' to a status of
'Forms Returned' it changes the colour of the respective 'Sales Ref' row on
sheet 'Sales' to 'Yellow'. Each 'Status' type (6 in total) would need it's
own colour.

Any suggestions on this much appreciated. I'm guessing this would need some
sort of VBA solution, looping through each 'Sales Ref' on sheet 'Sales'
giving each row it's appropriate colour based on it's most upto date status
in sheet 'Status'.

Please help! Much obliged...

Brian Taylor
Manchester, England

 

[Toppers]

Brian,
Add this code to your "Status" sheet (right click on tab==> view
code==>copy/paste).

Update "Status" and "cCode" arrays as required.


HTH

Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Variant

' Status values <=== Add further status values
Status = Array("Forms Out", "Forms Returned", "Sales Confirmed", "Cancelled")
' Colour codes <=== Add/update colour codes corresponding to Status above
cCode = Array(3, 4, 5, 6)

On Error GoTo wsexit
Application.EnableEvents = False

If Target.Row = 1 Then GoTo wsexit

Set isect = Application.Intersect(Target, Range("B:B"))
If Not isect Is Nothing Then
res = Application.Match(Target.Offset(0, -1).Value,
Worksheets("Sales").Range("A:A"), 0)
If Not IsError(res) Then
n = Application.Match(Target.Value, Status, 0)
Sheets("Sales").Rows(res).Interior.ColorIndex = cCode(n - 1)
else
Msgbox Target.offset(0,-1).value & " sales reference not found"
End If
End If
wsexit:
Application.EnableEvents = True
End Sub

 

[Co-op Bank]

Excellent answer, a huge help, thanks!

Brian,
Add this code to your "Status" sheet (right click on tab==> view
code==>copy/paste).

Update "Status" and "cCode" arrays as required.


HTH

Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Variant

' Status values <=== Add further status values
Status = Array("Forms Out", "Forms Returned", "Sales Confirmed", "Cancelled")
' Colour codes <=== Add/update colour codes corresponding to Status above
cCode = Array(3, 4, 5, 6)

On Error GoTo wsexit
Application.EnableEvents = False

If Target.Row = 1 Then GoTo wsexit

Set isect = Application.Intersect(Target, Range("B:B"))
If Not isect Is Nothing Then
res = Application.Match(Target.Offset(0, -1).Value,
Worksheets("Sales").Range("A:A"), 0)
If Not IsError(res) Then
n = Application.Match(Target.Value, Status, 0)
Sheets("Sales").Rows(res).Interior.ColorIndex = cCode(n - 1)
else
Msgbox Target.offset(0,-1).value & " sales reference not found"
End If
End If
wsexit:
Application.EnableEvents = True
End Sub