Newbie Needs Help.

[Neil]

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 are:

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

 

[Jim Thomlinson]

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

 

[Rick Rothstein]

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.