Conditional Formating based on method of input

[Jennifer C]

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

 

[Gord Dibben]

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.

Insert>name>define, 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 format>conditional 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: Insert>Module
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 Format>Conditional Formatting
Formula Is: =IsFormula(A1)
Click the Format button
Select the desired style(s)
OK out



Gord

 

[Jennifer C]

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

;1552521']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.

Insert>name>define, 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 format>conditional 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: Insert>Module
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 Format>Conditional 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

I am looking for assistance with having a cell change color if th 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 t 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 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.