Fill cells that are being used in a formula with color


Hello, hope someone can help me with a Excel question. 

[Screenshot attached but it doesn't show in my post]

I have a list of numbers in Column C2 to C26. In C28, I calculated the sum of several cells from the range C2 to C26. With C28 active, the formula is shown in the formula bar: =C2+C4+C5+C9+C12+C17+C20+C21+C26. if I click the formula bar, all the cells in the formula are selected. My question is: while the cells are selected, how can we fill them with color? 

Thank you, 




Hi Qing
Highlighting the cells used in a cell formula could be done using a VBA macro. Are you happy to do that and, if so, what colour should the cells be? Is your purosose to show other users what the sum is created using? 

You can't attach screen shots in the forum but attaching an Excel file is always helpful to us.

Please edit your question above to provide this detail.
John_Ru (rep: 502) Jan 13, '21 at 2:27 am
Please see Answer (I thought a VBA solution might be too much) and be sure you have the REVISION 1 answer/file.
John_Ru (rep: 502) Jan 13, '21 at 7:35 am
Add to Discussion




I decided that a non-VBA solution could be better for you (and should work irrespective of the complexity of the formula).

See the attached file where I've put your formula in cell C28 (with test values in the range A1:C26) and all the cells used in the formula of C28 are highlighted- change the formula in C28 and the cells will alter to suit (only if they're in C2 to C26 at present but that can be chnaged- see below).

The solution uses only conditional formatting. It involves a fairly complex formula to determine if a cell is mentioned in the formula in a given cell (C28 in you case)- the build up of that formatting formula is shown in the gold "box out" in cells F2:N17 of my file. The key bits are determining firstly the formula used in the desired cell (in bold below):


 (where the $ signs fix the address) and then getting the address of the first cell to be formatted (or not) using: 


(with no $ signs).

Excel function SUBSTITUTE strips out $ signs from both those and SEARCH checks to see if the stripped cell addess is in the stripped formula (and ISNUMBER is used to remove errors from the result of that, if not found).

The final formula (where values in bold might be changed in other workbooks) is 


To apply this, I selected cell C2 (the "variable" cell in the formula above), went on ribbon Home/Conditional Formatting/New Rule... and Use a formula to determine which cells to format. The text from above is then pasted under Format values..., you pick a Format (I used a light orange fill) then Okay. That formats only one cell but then you go back to Manage Rules (not New Rule) and carefully change Applies to from =$C$2 to =$C$2:$C$26 and click Ok. The rule then applies to C2 to C26.

If you really don't know which cells might be included in the formula of C28 (or wherever), you could change the range to apply to a much bigger range. You can Edit the rule to change the fill colour (or font colour, bold, italics etc.).  

Hope this is what you need.

REVISION 1: Replaced file and changed Answer to refer to cell C2 (slight error on original file)



@Qing - my answer seems to work in your case. My question below is for normally-active experts on the forum  
@Don, @Variatus - I created the answer above then later realised there might be a flaw. It works well when the formula has cell references in alphanumeric sequence but I later worried that if "C2" was at the end of the formula and the cell C20 wasn't in the formula, would the conditional formatting see the first instance as C2 as being in C20 and then shade cell C20 in error. It doesn't seem to (and it conditionally formats all the referenced cells correctly) but I don't know why- does Excel silently rearrange cell references in the background?
John_Ru (rep: 502) Jan 13, '21 at 1:37 pm
Add to Discussion

As your knowledge of Excel expands, it's important to understand that the cells referenced in your formula are not "selected". They are "highlighted" to show their association with the "selected" cell. Later, when you look at the Selection they aren't included. The reasons for which Excel highlights them and why you want them coloured are different and that must be borne out in the way you apply the colouring, if you really do want such colouring.

To answer your first qestion, Excel highlights the "dependent" cells. That is those cells that are referenced by the formula in any cell you select. You will find that a useful feature when you want to understand your formulas.

Your second question: You can use Conditional Formatting to colour cells according to specific rules which you can freely set. For example, you might want to highlight any of the cells in your formula that don't have a value, so as to alert the user to enter something in them. Or you might want to colour cells that don't contain a number (including those that are blank). You make the rules. 

Just to colour all of them should be done by worksheet design, such as placing a border around them or having a caption next to them. One wouldn't use colour for that if the intention is to use colour for to convey other messages.

By the way, you might want to consider the SUM() function for your formula.


The SUM() function can ignore faulty cell contents and just add up the numbers.


Answer the Question

You must create an account to use the forum. Create an Account or Login