How to check if a cell is part of a series of cells (not a neat ra
 

Hi All,

I want to find out if a changed cell was one of a number of cells.
The cells i want to test for are B10,B15,B20,B25,B30 etc. upto B80.

I can use the IF statement with a lot of ORs to check this but surely there
is another, cleaner way to do this. Can anyone tell me how?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$10" OR
Target.Address = "$B$15" OR
Target.Address = "$B$20" OR ....... THEN

code to execute

END IF

end sub

How to check if a cell is part of a series of cells (not a nea
 

Thanks TDW,

It's cleaner code than my solution.

I'm wondering if something could be done with a name definition.
I can select the B10,B15,B20 etc cells and give them a name, but i could not
find a way to check on that name. Something like

IF Target.Name = "MyName" THEN

I tried this but it did not work. Any ideas in that direction?


"tdw" wrote:

Hi,

Basically, you want to know if you're in column 2 and row 10, 15, 20, ...
80. Your row numbers are multiples of 5 which can be verified using modulus
arithmetic.

The following code will perform that check for you:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And _
2 <= Target.Row \ 5 And Target.Row \ 5 <= 16 And _
Target.Row Mod 5 = 0 Then
MsgBox "I'm in one of the target cells"
End If
End Sub

It would be slightly faster if you used With - End With so that Target is
not referenced multiple times.

HTH,
tdw


--
Timothy White
Contract Programmer
Ontario, Canada

<my initialshite<atsympatico<dot<countryCode



"JeKaRe" wrote:

Hi All,

I want to find out if a changed cell was one of a number of cells.
The cells i want to test for are B10,B15,B20,B25,B30 etc. upto B80.

I can use the IF statement with a lot of ORs to check this but surely there
is another, cleaner way to do this. Can anyone tell me how?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$10" OR
Target.Address = "$B$15" OR
Target.Address = "$B$20" OR ....... THEN

code to execute

END IF

end sub

How to check if a cell is part of a series of cells (not a nea
 

Glad to help out,

In answer to your second question, I think you could use a named range along
with the Intersect() function. Have a look at the "worksheet_Change with
Formulas" posting from earlier today.

Feel free to e-mail me directly if you have any more questions; my e-mail
address can be found in my signature below.

HTH,
tdw


--
Timothy White
Contract Programmer
Ontario, Canada

<my initialshite<atsympatico<dot<countryCode

How to check if a cell is part of a series of cells (not a nea
 

Thank You Timothy!

That is just what I was looking for. Now i can put any cell i want in a
named range and use

If Not ApplicationIntersect(Target, Worksheets(1).Range("MyNamedRange")) Is
Nothing Then

Works like a charm.
Thanks again and have a nice evening (at least in this timezone ;-)

Greetings, Jack (from the Netherlands)


"tdw" wrote:

Glad to help out,

In answer to your second question, I think you could use a named range along
with the Intersect() function. Have a look at the "worksheet_Change with
Formulas" posting from earlier today.

Feel free to e-mail me directly if you have any more questions; my e-mail
address can be found in my signature below.

HTH,
tdw


--
Timothy White
Contract Programmer
Ontario, Canada

<my initialshite<atsympatico<dot<countryCode