Fill cells that are being used in a formula with color

Qing

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):

=FORMULATEXT($C$28)

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

=CELL("address",C2)

(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 

=ISNUMBER(SEARCH(SUBSTITUTE(CELL("address",C2), "$", ""),SUBSTITUTE(FORMULATEXT($C$28), "$", "")))

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)

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.

=SUM(C2,C4:C5,C9,C12,C17,C20:C21,C26)

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