formula dependant and independant value

[sujittalukde]

Can a macro be provided to change the color of values in a cell of a sheet for the following:

1. The figures or values which are a result of formula in a sheet to color say red

2. the values which are consonant but used for formula in any sheet in a workbook to color say ble

3. The values which are neither a result of formula nor used for formula to stay black

 

[Lewiy]

I assume that the purpose of this is to identify the 3 different types of cell as you have defined. In which case, you could always use Edit>GoTo>Special and then select either Precidents, Dependants, or Constants as desired.

 

[Jonmo1]

Sub test()
For Each Cell In Range("a1:G10")
    Cell.Font.ColorIndex = -4105
    x = ""
    On Error Resume Next
    x = Cell.Dependents.Address
    If x <> "" And Cell.HasFormula = False Then
        Cell.Font.ColorIndex = 5
    End If
    If Cell.HasFormula = True Then
        Cell.Font.ColorIndex = 3
    End If
Next Cell

End Sub

 

[sujittalukde]

THanks to duo