HasFormula to find only specific formulas

[strud]

I am trying to format cells based on whether they contain a refernce to a named range - i.e.

Please Login or Register  to view this content.

I know how to use HasFormula to find any cells containing formulas but don't know how to narrow this down. So I currently have:

Please Login or Register  to view this content.

Any ideas on how I can exclude all formulas except references to named ranges?

Strud

[JBeaucaire]

You don't need to test with HasFormula, you can set your range using the SpecialCells method and choose only formula cells:

Please Login or Register  to view this content.

[strud]

[QUOTE=JBeaucaire;3845576]You don't need to test with HasFormula, you can set your range using the SpecialCells method and choose only formula cells:

Thanks that does work, but is there a way to do it without specifying the range names in the code, as this isn't really suitable as a solution for me(?)

[JBeaucaire]

The solution offered is based on the question asked. Can you rephrase your specifications?

[strud]

Well, I tried to say it in the first specification - maybe it is not clear sorry.

But I am looking for a way to test if the cells contain a named range of any description NOT a specific range with a specific name.

So that when I add a new defined range I do not have to add it into the code. So the code tests whether the cell contains a reference to ANY named range.

I could standardise my named ranges and test for a formula containing a specific word- could work but I don't know how to code it.

[romperstomper]

You'd have to loop through the names in the workbook populating an array/collection/dictionary or whatever with the names, then for each formula cell you'd have to test each name (exiting after you found one).

[strud]

Ok thanks romperstomper, I was hoping that was not going to be the case, seems like less work to go with JBeaucaire's solution to be honest, so I'll just enter the names into the code.

Thanks Guys!

[JBeaucaire]

I don't know, this seems like it should work and is pretty simple:

Please Login or Register  to view this content.

[romperstomper]

To be thorough, you'd need to cater for local names used on the same worksheet too - e.g. if you have a range named Sheet1!somename and a formula on sheet1 refers to it, the formula would probably only use =somename, not =Sheet1!somename (though it could use either).

[strud]

Thanks JBeaucaire that works perfectly.

Romper I hear what you're saying but JBeaucaire's solution works fine for my needs in this instance.

Cheers guys!

[shg]

That code would also find a name that is a substring of another name, e.g., Bob in Bobo.

[JBeaucaire]

Great tip, shg. I believe named ranges that are possibly substrings of other named ranges would be ok in this project since he's flagging all named ranges references anyway.

If the formula references a named range that is a substring of an actual SHEET name, then we would get false results here.

So a Named Range of "MyCat" would accidentally give a TRUE match in a cell that referenced a sheet called "MyCatalogs". Strud, be sure to check for that possibility by reviewing your named ranges and your sheet names.