Forum Discussion
Cells font color based on input formula in Excel
Hi!
I would like to change the font color of the cells of row "K" based on the input formula. For example if the formula is=$C$2*J9 then the font color of cell "K9" should be blue, if the formula is =$E$2*J9 then should be red. So if the formula contains $C$2 then blue, if it contains $E$2 then red.
Its a big table with many rows and columns and it's a bit slow to color the cells by hand.
The conditional formatting is not working in this case so is there any other idea for this? Is it possible to do it without macros? It's (hopefully) only one project, so some "simple" solution would be great (if it exist). If only macro works then let be macro.
Thank you!
Hi ballatoma
Provided you run Excel >/= 2016. Cond. Format rule with formula, for Blue font:
=ISNUMBER( SEARCH( "$C$2", FORMULATEXT( K9 ) ) )NB: not clear what your 1st row of data is, so adjust K9 if necessary
Then setup a 2nd rule for Red font similarly changing $C$2 with $E$2
AND as "Its a big table" don't forget to check the box Stop if true next to the 1st rule
5 Replies
Select all rows from column K, go to Conditional formatting on the home tab of the ribbon, select new rule and then "use a formula to determine which cells to format".
Assuming the only distinction between formatting in blue or red is by containing $C$2 or $E$2 respectively;
Input the following formulation for blue formatting:
=ISNUMBER(SEARCH("$C$2";FORMULATEXT(K9)))And this one for red formulation:
=ISNUMBER(SEARCH("$E$2";FORMULATEXT(K9)))Thank You!
Working perfectly!
Hi ballatoma
Provided you run Excel >/= 2016. Cond. Format rule with formula, for Blue font:
=ISNUMBER( SEARCH( "$C$2", FORMULATEXT( K9 ) ) )NB: not clear what your 1st row of data is, so adjust K9 if necessary
Then setup a 2nd rule for Red font similarly changing $C$2 with $E$2
AND as "Its a big table" don't forget to check the box Stop if true next to the 1st rule
