Conditional Formatting to reflect manual input of data

HI, I need some advise for conditional formatting in Excel.

I am trying to build a forecasting model in excel. It is a large workbook that involve lots of formulas. sometimes user might want to remove the formula and manually input the figures to change the assumptions to reflect the current status.

How can I use conditional formatting that the cell will reflect a different font color or cell color when the figures in the cell is manually input?

For Example

Assumption - Cost for the next 5 years will be inflated by 10%

Year 0 = $100 (assuming this cell is A2)
Year 1 = A2 * 110%
Year 2 = A3 * 110%
Year 3 = A4 * 110%
Year 4 = A5 * 110%
Year 5 = A6 * 110%

But when Year 1 comes close to ending, user might want to replace the formula with absolute figures or their simplified addition

Year 0 = $100 (assuming this cell is A2)
Year 1 = $120
Year 2 = A3 * 110%
Year 3 = A4 * 110%
Year 4 = A5 * 110%
Year 5 = A6 * 110%


Is it possible to use conditional formattign to reflect Year 1 figure as diffent font color or cell is a different color because it is a manual input. OR maybe there is other way to reflect this?

You have a couple of choices. Upgrade to Excel 2013 which has a new ISFORMULA function or, in earlier versions, use a VBA User Defined Function (UDF).

http://www.ozgrid.com/VBA/ExcelIsFormula.htm

use this as your CF formula in A3 (repeat A3) and applied to all cells below.

=A3<>1.1*A2

Hi, that means i need to set a new formula in the conditional formatting for every cells? That is a lot of cells to insert formula in the conditional formatting

No, just select A3 down and apply the formula. This is a specific solution in that it is testing the actual value against the anticipated calculated value.

However, given you have ruled out the options I suggested, you don't have many choices.

Bear in mind that the solution Glenn has offered might not highlight cells that just happen to be 10% greater than the previous entry.

No. You do it once and copy the format to all cells. A one-step operation. Did you look at the sheet?