Highlight cell references in formula

blimbert

New Member
Joined
Jan 25, 2005
Messages
14
Good morning - I have an excel file with hundreds of rows containing data inputs and formulas. The file is quite complex, and needs to be distributed. I think some people may get confused on exactly what the forumlas are doing, so I was hoping to be more blatant about identifying the cells the formula references by simply highlighting the background of the reference cells when a cell with a formula is selected.

I know that when a cell is edited that contains a formula the reference cell's border is highlighted a certain color, I'm trying to modify this by not having to edit the cell with the formula, as well as changing the background color.

For example, if cell A3 contained =A1+A2, when cell A3 was selecting, cell A1 would be highlight with some color, and cell A2 would be highlighted with another.

Any ideas on the coding?
Thanks,
Brian
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
Welcome to the board!

Can't you just press F2 in the cell with the formula?

Or use auditing --> trace precedents?
 

blimbert

New Member
Joined
Jan 25, 2005
Messages
14
Thanks for the quick response, Oaktree. My concern with the default method is twofold. 1) Because there's so many forumals and cells, I don't want to have to make users do this method manually. 2) Because my user audience is less skillful with excel.

Hence, I'm trying to modify this by not having to edit the cell with the formula, as well as changing the background color.

Any thoughts?
Regards,
Brian
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
Try Greg Truby's suggestion at the bottom of this thread:

http://mrexcel.com/board2/viewtopic.php?t=107297&highlight=highlight+precedents#javascript:void(0);
 

Forum statistics

Threads
1,147,510
Messages
5,741,574
Members
423,668
Latest member
Audorin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top