change color of cells that are in my current formula

[Jenni_L]

Hello,

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+J2713), how do tell Excel to change those 5 cells to a different color? 

[#2]

@Jenni_L 

Maybe with VBA...

Sub SearchFormulaContents()
     Dim c As Range
     Dim firstAddress As String
     Dim vSearchValue As Variant
     Dim blnVglTotalCellContent As Boolean
   
     vSearchValue = "=A1"
     blnVglTotalCellContents = True
   
     With Table1.Columns(1) 'Customize
         .Interior.ColorIndex = xlColorIndexNone
         Set c = .Find(vSearchValue, , xlFormulas, xlPart)
         If Not c Is Nothing Then
             firstAddress = c.Address
             do
                 If blnVglTotalCellContents Then
                     If c.Formula = vSearchValue Then _
                     c.Interior.ColorIndex = 6
                 else
                     c.Interior.ColorIndex = 6
                 End If
                 Set c = .FindNext(c)
                 If c Is Nothing Then Exit Do
             Loop While c.Address <> firstAddress
         End If
     End With
end sub

OR...
You insert the following code into a standard module:
Code:

OptionExplicit
Function =ISFORMULA(rg As Range) As Boolean
=ISFORMULA = IIf(rg.HasFormula, True, False)
End function

The new function can be used in any cell with =ISFORMULA(reference) or as a rule description for conditional formatting.

Hope I could help you with these information.

NikolinoDE

I know I don't know anything (Socrates)