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

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

Hi Don,

Try:

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

---
Regards,
Norman


"Don Niall" wrote in message
...
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

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


.

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


.

Norman - Many thanks! There was indeed an error in my code.
One small thing I noticed:
If B4 (for example) is set to "RED" and the w/s saved, and
closed. When I re-open the w/s the macro doesn't fire (or
at least doesn't appear to)? I would have thought once I
get the prompt to 'Enable Macro's' it would fire, and
trigger the outputs required ......??

Any thoughts? (I guess I need to get more conversant with
macros, eh?!!)

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

Try:

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

---
Regards,
Norman


"Don Niall" wrote in message
...
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


.

No. Macros run when you run them, or when they are called from other macros.

Imagine you had a "delete files" macro for some spesific cleaning task,
would you want it to delete files as soon as you enable macros in general
on opening ?

HTH. Best wishes Harald

"Don Niall" skrev i melding
...
Norman - Many thanks! There was indeed an error in my code.
One small thing I noticed:
If B4 (for example) is set to "RED" and the w/s saved, and
closed. When I re-open the w/s the macro doesn't fire (or
at least doesn't appear to)? I would have thought once I
get the prompt to 'Enable Macro's' it would fire, and
trigger the outputs required ......??

Any thoughts? (I guess I need to get more conversant with
macros, eh?!!)

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

Try:

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

---
Regards,
Norman


"Don Niall" wrote in message
...
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


.

Hi Don,

If you want the macro to fire when the workbook is opened, try the
following:

Assuming your macro is in a standard module (rather than a sheet or the
thisworkbook module),
(1) Right-click the Excel icon to the left of 'File' on your menu bar
(2) Select 'View Code'
(3) Paste the following code

Sub workbook_open()
Call Tester '<------- REPLACE !!
End Sub

Replace Tester with the name of your macro.

It would be well worth your time looking at event procedures and, more
particularly, the discussion
http://www.cpearson.com/excel/events.htm
on Chip Pearson's web site.

---
Regards,
Norman


"Don Niall" wrote in message
...
Norman - Many thanks! There was indeed an error in my code.
One small thing I noticed:
If B4 (for example) is set to "RED" and the w/s saved, and
closed. When I re-open the w/s the macro doesn't fire (or
at least doesn't appear to)? I would have thought once I
get the prompt to 'Enable Macro's' it would fire, and
trigger the outputs required ......??

Any thoughts? (I guess I need to get more conversant with
macros, eh?!!)

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

Try:

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

---
Regards,
Norman


"Don Niall" wrote in message
...
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


.