Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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.
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
|
|||
|
|||
thank you Gord, I will pass along the info to my user who needed this!
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formating for the whole row, based on one cell value | Excel Discussion (Misc queries) | |||
Conditional Formating based on Date | Excel Worksheet Functions | |||
Conditional Formating based on a different cell | Excel Discussion (Misc queries) | |||
Conditional Formating Based on Date | Excel Discussion (Misc queries) | |||
Conditional formating based on text | Excel Worksheet Functions |