Conditional Formating based on method of input
 

I am looking for assistance with having a cell change color if the cell data was put in/changed manually rather than through a formula. For instance, I have a cost spreadsheet that is using a reference file to to input data in the cells, however the reference sheet cost data may be based on 10,000 pieces and I know that there is a price break if I order 20,000 piece so I change the cost in the cell to reflect the different cost. I then want that cell to change color b/c the data did not come from the reference file/formula and was put in manually.

Conditional Formating based on method of input
 

Either of two methods could be used.

You can use Excel 4 macro in a defined name formula to check if cell
has a formula.

Insertnamedefine, in the source box put

=GET.CELL(6,Sheet1!$A1)

and in the name box type in something descriptive like

FormulaIs

click OK

Select A1 then formatconditional formatting, select formula
is and use

=LEFT(FormulaIs)="="

Set desired formatting style and OK your way out.

Second method is a UDF.........................

Function IsFormula(cell_ref As Range)
IsFormula = cell_ref.HasFormula
End Function


To use this:


Open the VBE editor: ALT F11
Open the Project Explorer: CTRL R
Locate your file name in the project explorer pane on the left.
Right click the file name
Select: InsertModule
Copy the code above and paste into the window on the right
Return back to Excel: ALT Q


Set the conditional formatting...
Select the cell(s) in question. Assume this is cell A1.
Goto FormatConditional Formatting
Formula Is: =IsFormula(A1)
Click the Format button
Select the desired style(s)
OK out



Gord



On Tue, 6 Mar 2012 18:09:46 +0000, Jennifer C
wrote:


I am looking for assistance with having a cell change color if the cell
data was put in/changed manually rather than through a formula. For
instance, I have a cost spreadsheet that is using a reference file to to
input data in the cells, however the reference sheet cost data may be
based on 10,000 pieces and I know that there is a price break if I order
20,000 piece so I change the cost in the cell to reflect the different
cost. I then want that cell to change color b/c the data did not come
from the reference file/formula and was put in manually.

thank you Gord, I will pass along the info to my user who needed this!