Change cell color of all cells in my formula

jlindgren
Posts
1
Registration date
Tuesday May 31, 2022
Status
Member
Last seen
May 31, 2022
- May 31, 2022 at 03:48 PM
JessyLew
Posts
3
Registration date
Thursday June 2, 2022
Status
Member
Last seen
June 3, 2022
- Jun 2, 2022 at 05:12 AM
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

2 replies

vcoolio
Posts
1347
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
June 10, 2022
249
Jun 1, 2022 at 11:45 PM
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.
0
JessyLew
Posts
3
Registration date
Thursday June 2, 2022
Status
Member
Last seen
June 3, 2022
1
Jun 2, 2022 at 05:12 AM
If there aren't many cells, I would highlight each one and paint it the color you want.
0