Highlight Cells that are referenced by other sheets

Hi all,

I have about 30 sheets (1,2,3 and so on) with data in and 1 sheet (main) that combines all that data into 1 sheet.

Is there a way i can have two buttons that will toggle the ability to highlight all cells that reference another sheet.

Eg: If cell A1 on the (main) sheet has ='Sheet1'!B1 then highlight cell A1 on the main sheet.

One button will highlight and the other will un-highlight.

I need to do this on the main sheet for references to all other sheets.

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do in more detail (two words won’t do).

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Randall,

Add a Module and post this, and add button on the Main sheet.

It cycles through all the "used range" on the Main sheet, and finds all cells with a formula. If the formula includes the word "Sheet", it fills the cell.

Please Login or Register  to view this content.

Two points to consider:
If any cells on the Main sheet include the words "Main sheet" in the formula, they will be filled as well!
This "highlight" may conflict with any other Conditional Formatting you have in the cells affected

Ochimus