Clear cells if adjacent cell contains certain text from a direct link

[Groovicles]

Hi everyone,

I have the following code:

Please Login or Register  to view this content.

What I'm trying to do is find any cell in column B range that contains the letters XYZ in it (part of a larger string). If so, it should clear contents in the same row from columns D to Y. Two caveats:

1. the values in column B are derived from a direct link to another cell
2. I've tried using only text values and it still doesn't work

Any ideas?

Thanks!!

[walruseggman]

Edit: Forgot the lookin, very important per your specs

No need to loop through every cell:

Please Login or Register  to view this content.

[Groovicles]

Hi walrus,

Thanks very much. Your code works great when the look up value is a string typed into the cell, but what if the cell value is derived from a direct link to another sheet?

Thanks!

[walruseggman]

It should work for that, too.

If not, can you post an example workbook so that I can see exactly how your direct link is working?

[Groovicles]

He Walrus,

The cells in question are direct-linked as such: =Summary!$B$5. All links are absolutely values, but I've tried relative and that still didn't work. All cells are directly linked in that manner though.

[walruseggman]

That code should work just fine assuming you're using the code after i edited in the lookin:=xlValues.

Again, if it is not, please post an example workbook demonstrating it not working, because the situation you describe is exatly what lookin:=xlValues is meant to address.

[Groovicles]

Ah, I didn't see the edit! Works perfect. Thx!