How to determine if a formula or a manual entry has been written in a cell

Question

I have a cell/ entire column that has a formula(s) in it. Sometimes I go into that cell and manually change that value to a different value. The formula goes away and is being over written by the manual entry. How can someone determine later down the line that that cell was being entered automatically with the formula or entered manually? Is there some rule or trick within conditional formatting or elsewhere that can indicate to the viewer that this cell was overwritten or that cell is being formulated by the generic formula.

An example....

if the formula adds a selected range and gives a value and that cell is red now. If I enter a value manually in that cell, that cell now sees that value and turns it 'blue' because it was manually entered. Is there logic in excel that will differentiate this? Thanks

Answer 1

UPD:

1) add UDF:

Function hasFormula(r As Range) As Boolean
    hasFormula = r.hasFormula
End Function

2) Select all cells and apply following CF rules:

• Blue CF (for values): =AND(A1<>"",NOT(hasFormula(A1)))
• Red CF (for formulas): =hasFormula(A1)

---

For Excel 2013 you could use built-in function ISFORMULA:

Select all cells and apply following CF rules:

• Blue CF (for values): =AND(A1<>"",NOT(ISFORMULA(A1)))
• Red CF (for formulas): =ISFORMULA(A1)

Alternative way (for higlighting all cells with formulas):

Press CTRL+G, then select "Special..."->"Formulas" and press "OK". But it highlights cells only temporary, untill you select any other cell.