Change cell color of all cells in my formula
jlindgren
JessyLew
- Posts
- 1
- Registration date
- Tuesday May 31, 2022
- Status
- Member
- Last seen
- May 31, 2022
JessyLew
- Posts
- 3
- Registration date
- Thursday June 2, 2022
- Status
- Member
- Last seen
- June 3, 2022
Related:
- Change cell color of all cells in my formula
- Excel change font color based on value of another cell ✓ - Forum - Excel
- Excel vba - change cell color based on value ✓ - Forum - Programming
- Change cell color based on dropdown selection ✓ - Forum - Excel
- Conditional formatting change font color based on another cell ✓ - Forum - Excel
- Change cell color if another cell contains text ✓ - Forum - Excel
2 replies
vcoolio
Jun 1, 2022 at 11:45 PM
- Posts
- 1347
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- June 10, 2022
Jun 1, 2022 at 11:45 PM
Hello JLindgren,
Perhaps a sub routine as follows is all that you may need:-
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.
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
Jun 2, 2022 at 05:12 AM
- Posts
- 3
- Registration date
- Thursday June 2, 2022
- Status
- Member
- Last seen
- June 3, 2022
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.