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