Hi Don,

The use of data validation should have no relevance and the my code works
for me.

Are you SURE that you are using the code that I suggested? If you didn't
paste the code into your module, then it is possible that you have a
transription error.

---
Regards,
Norman.

"Don Niall" wrote in message
...
Thankyou! This worked ...... well very nearly!!!
The problem I am now encountering is, if the value is set
to anything OTHER THAN "RED" it still triggers the same
outcomes (ie the identifed cells are set to zero, and the
contents of 1 cell are cleared).

The "RED" identifier is coming directly from a Data
Validation list I have specified in cells B4 - B17. There
are three other entries in the same list(GREEN, BLUE,
YELLOW). Is it possible that the macro 'see's' ALL values
in the List - irrespective of which one actually appears
in the cell? If so how do I get around this?

Many thanks,

Don-
-----Original Message-----
Hi Don

Sub test()
Dim myRange As Range, MyCel As Range
Set myRange = Range("B4:B17")
For Each MyCel In myRange.Cells
If MyCel.Value = "RED" Then
'stuff...
End If
Next
End Sub

This is good:
http://www.j-walk.com/ss/books/xlbook25.htm

HTH. Best wishes Harald


"Don Niall" skrev i melding
...
Hi - I would appreciate some input on the macro below. I
apologise in advance as I am just getting familiar with
macros. The macro is working but only on the first cell
B4. I need it to step through each cell from B4 to B17,
and do the same procedure ....I am guessing I need some
kine of For/Netxt statement - or some kind of loop
condition? Would really appreciate some help?
(PS Any good resources available - books or others - on
Macros for beginners?)

Don-

Dim myRange As Range
Set myRange = Range("B4:B17")
If myRange.Value = "RED" Then
With myRange
.Offset(0, -1).Value = ClearContents
.Offset(0, 1).Value = 0
.Offset(0, 2).Value = 0
.Offset(0, 4).Value = 0
End With
End If
End Sub


.