Newbie Needs Help.
I've been thrown in at the deep end with a spreadsheet at work and need some
help with some VBA code (I know a bit of VB.net but no VBA) Details a 1. We have a sheet with data in columns A-K 2. Data in column 'D' is changed and depending on the contents of the cell in column 'D' I need to colour the entire row a particular colour. (There are aroun ten different things that could be in column 'D') My solution outline is: 1. When a cell is changed, put the contents of the cell into a string variable. 2. Use a Case....Select Statement to test the contents of the variable. 3. Depending on the value of the variable change the entire row (but only columns A-K) to the appropriate colour. Examples: The text 'CAD / CAM' is entered into cell D3 - Cells A3-K3 should be coloured Red. The text 'Model' is entered into Cell D5 - Cells A5-K5 should be coloured blue. If anyone can give me an idea of how to accomplish this, or point me in the direction of some help pages I'd be grateful. I think we only need to run this in Excel 2003 if that makes a difference Thanks in advance Neil |
Newbie Needs Help.
You are looking for event code that works on the sheet change event. Right
click the worksheet and select view code. Above the code window on the left is a list of the objects associated with the worksheet. Switch it from General to Worksheet. To the right of that is a listing of the events. Select Change. At this point you will have 2 code stub. You can delete the one for selection change. Private Sub Worksheet_Change(ByVal Target As Range) End Sub Target is the cell or cells that were changed. Here is a simple version of what you want Private Sub Worksheet_Change(ByVal Target As Range) if target.column = 4 then select case target.value case "This", "That" cells(target.row, "A").resize(1, 11).interior.color = 34 case "other" cells(target.row, "A").resize(1, 11).interior.color = 35 case else cells(target.row, "A").resize(1, 11).interior.color = xlnone end if End Sub -- HTH... Jim Thomlinson "Neil" wrote: I've been thrown in at the deep end with a spreadsheet at work and need some help with some VBA code (I know a bit of VB.net but no VBA) Details a 1. We have a sheet with data in columns A-K 2. Data in column 'D' is changed and depending on the contents of the cell in column 'D' I need to colour the entire row a particular colour. (There are aroun ten different things that could be in column 'D') My solution outline is: 1. When a cell is changed, put the contents of the cell into a string variable. 2. Use a Case....Select Statement to test the contents of the variable. 3. Depending on the value of the variable change the entire row (but only columns A-K) to the appropriate colour. Examples: The text 'CAD / CAM' is entered into cell D3 - Cells A3-K3 should be coloured Red. The text 'Model' is entered into Cell D5 - Cells A5-K5 should be coloured blue. If anyone can give me an idea of how to accomplish this, or point me in the direction of some help pages I'd be grateful. I think we only need to run this in Excel 2003 if that makes a difference Thanks in advance Neil |
Newbie Needs Help.
Something the OP might want to consider to reduce the typing (or internal
copy/pasting) is to use a With/End With block with your code... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 4 Then With Cells(Target.Row, "A").Resize(1, 11).Interior Select Case Target.Value Case "This", "That" .Color = 34 Case "other" .Color = 35 Case Else .Color = xlNone End Select End With End If End Sub I also added the End Select statement that you accidentally omitted from your posted code Neil.. take note... if you chose to stick with Jim's originally posted code, you will have to add the End Select statement immediately *before* the End If statement. -- Rick (MVP - Excel) "Jim Thomlinson" wrote in message ... You are looking for event code that works on the sheet change event. Right click the worksheet and select view code. Above the code window on the left is a list of the objects associated with the worksheet. Switch it from General to Worksheet. To the right of that is a listing of the events. Select Change. At this point you will have 2 code stub. You can delete the one for selection change. Private Sub Worksheet_Change(ByVal Target As Range) End Sub Target is the cell or cells that were changed. Here is a simple version of what you want Private Sub Worksheet_Change(ByVal Target As Range) if target.column = 4 then select case target.value case "This", "That" cells(target.row, "A").resize(1, 11).interior.color = 34 case "other" cells(target.row, "A").resize(1, 11).interior.color = 35 case else cells(target.row, "A").resize(1, 11).interior.color = xlnone end if End Sub -- HTH... Jim Thomlinson "Neil" wrote: I've been thrown in at the deep end with a spreadsheet at work and need some help with some VBA code (I know a bit of VB.net but no VBA) Details a 1. We have a sheet with data in columns A-K 2. Data in column 'D' is changed and depending on the contents of the cell in column 'D' I need to colour the entire row a particular colour. (There are aroun ten different things that could be in column 'D') My solution outline is: 1. When a cell is changed, put the contents of the cell into a string variable. 2. Use a Case....Select Statement to test the contents of the variable. 3. Depending on the value of the variable change the entire row (but only columns A-K) to the appropriate colour. Examples: The text 'CAD / CAM' is entered into cell D3 - Cells A3-K3 should be coloured Red. The text 'Model' is entered into Cell D5 - Cells A5-K5 should be coloured blue. If anyone can give me an idea of how to accomplish this, or point me in the direction of some help pages I'd be grateful. I think we only need to run this in Excel 2003 if that makes a difference Thanks in advance Neil |
All times are GMT +1. The time now is 01:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com