Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign a particular colour to rows based on their current status
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign a particular colour to rows based on their current status
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign a particular colour to rows based on their current stat
Excellent answer, a huge help, thanks!
"Toppers" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting: colored bar with pointer on current status | Excel Discussion (Misc queries) | |||
how do i assign a cel a colour system on a monthly report? | Excel Discussion (Misc queries) | |||
Assign a Colour to part of a Cells Text? | Excel Programming | |||
Code to change interior colour only if current interior colour is | Excel Programming |