change cell color of all cells in my formula

Question

I often have huge spreadsheets where I have to add random cells together. In order to make sure I don't miss any cells, I want to change the color of my cells that are in my formula. So if my formula is SUM(A2+B14+A132+E719), how do tell Excel to change those 4 cells to a different color? 



System Configuration: Windows / Chrome 101.0.4951.67

vcoolio · Answer

Hello JLindgren,

Perhaps a sub routine as follows is all that you may need:-

Sub Test()
        
        Dim rng As Range
        Dim r As Range

Sheet1.Range("A1").Formula = "=SUM(F1+G3+H4+I5+J10+N4)"
        Set rng = Range("A1").Precedents

For Each r In rng.Areas
             r.Interior.ColorIndex = 8
        Next r

End Sub

The code uses the Precedents(Trace Precedents) method which creates trace points to the cells referred to in a formula, or, in other words, it tracks and points to the cells referred to in a formula.
Thus, assuming that the formula is in cell A1 of Sheet1, the code will track the cells referred to in the formula and high-light them in color index 8 (cyan, I think!).
I've attached a sample workbook showing how this works. Click on the "RUN" button to execute the code.

Here is the link to the file:-

https://wetransfer.com/downloads/fe26671604a67e49f000ce1eb6bc402520220602034157/5b3754

I hope that this helps.

Cheerio,
vcoolio.

JessyLew · Answer

If there aren't many cells, I would highlight each one and paint it the color you want.

Change cell color of all cells in my formula

2 replies

Your reply

Subscribe To Our Newsletter!