Format a cell on whether a plain number is entered or the number is the result of a typed formula.

[Khalabeeb]

Hi all,

I am currently looking to apply a conditional formatting for this specific situation:

- If I enter a plain value in a cell, let's say "10", the cell is colored red

- If I enter the same value but via a formula calculation, let's say "=2*5" or "=A3*B4" with A3=2 and B4=5, I would like the cell to be colored in yellow.

Is that possible?

Thanks in advance

 

[dave2018]

Put this condition, to test A1:

=ISNA(FORMULATEXT(A1)) Will color if the cell was typed out.
=NOT(ISNA(FORMULATEXT(A1))) Will color if the cell has a formula

 

[Khalabeeb]

Hi dave,

Thanks for the help! It works but I have one question in regards to your answer:

- The formula applies to the "A1" cell. How would I do if I wanted the whole sheet, or a big part of it, to function under this rule? Because copy/pasting the formula for every cell would be really time consuming.

Thanks in advance.

 

[dave2018]

highlight all the cells u want to format condintionally (select the cells starting from the top left of what you want, so that the top-left most cell is active (selected, but the color is white as opposed to the other selected cells). open conditional format and make a new rule. add the formula. make sure you change the A1 to the top-left most cell in your selection.

Option 2: under manage rules (under conditional formating in the home ribbon) u can change the "applies to" field next to your rule, and update it to refrence the block of cells that u need to format.