VBA Help - If statement equal to a range of cells.

[danjames11]

I have a range of cells going from B13 all the way to B400. I know how to write this:

If ActiveCell = Range ("B13,B14,B15,B16,B18,and so on") Then....

But I want to be able to do something like B13:B14 instead of writing all the way to 400, But B13:B400 does not work.

Anyone know how to do this.. Once again, I want to see if the active cell is equal to any of the cells in the the range B13 To B400.

Thanks.

 

[VoG]

Try

If IsNumeric(Application.Match(ActiveCell.Value, Range("B13:B400"), 0)) Then

 

[danjames11]

No this didnt work, Thanks Anyway. Any Other Ideas?

It showed the False when I tried this, Thanks

 

[sous2817]

Are you sure you have a match? I tried VoG's suggestion and it worked.

Just as a test, let's say your active cell is D7, have you tried the Match formula in your worksheet to see what it returns?

=MATCH(D7,B13:B401,0)

If you have a match it will return a number, otherwise you'll get the #N/A error. If I had to guess, I'm thinking you don't have a match eventhough you think you do (leading or trailing spaces perhaps or you're dealing with a number formatted as a number in one cell and a number formatted as text in the other).

 

[danjames11]

Ahh right, yeah its working now Many thanks to you, I'm not sure why I didnt seem to get it to work in the first place but its fine now. You'r very talented and thanks alot!