2 years ago
how to change text color in excel for any cell that has some sort of calculation
I want to be able to distinguish plain data from data that was derived from a formula or calculation in the cell. If the cell has a calculation, i want to change text to a different color. Doesn't seem to have an option to do something like this using Conditional formatting.
Separate topics with commas, or by pressing return. Use the delete or backspace key to edit or remove existing topics.
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$1 Answer
You are right - there is no conditional test to detect if a cell has a formula or not. :-(
The list of available tests is here: http://office.microsoft.com/en-us/excel/CH100645021033.aspx
Conditional formatting is the right solution though.
I think your only approach wil be to use a VBA macro to do the work for you.
Here is how to setup Excel 2007 to do VBA programming:
http://msdn.microsoft.com/en-us/library/bb226688(v=office.11).aspx
Here is how to make a general module
http://www.jlathamsite.com/Teach/Excel_GP_Code.htm
Once made - use this custom function to return the Cell's formula:
Function CellFormula(Rng As Range) As String
CellFormula = Rng.Formula
End Function
Then enter this on a blank cell in your worksheet:
=CellFormula(B5)
and you will get the resulting formula from B5 rather than its value.
Then I suggest you use a conditional and check to see if "SUM" or (whatever the formula you are trying to check for) is in the string - if it is then color it.
or even just check to see if the CellFormula result is not a number
I.e. use ISNONTEXT or ISNUMBER functions
The list of available tests is here: http://office.microsoft.com/en-us/excel/CH100645021033.aspx
Conditional formatting is the right solution though.
I think your only approach wil be to use a VBA macro to do the work for you.
Here is how to setup Excel 2007 to do VBA programming:
http://msdn.microsoft.com/en-us/library/bb226688(v=office.11).aspx
Here is how to make a general module
http://www.jlathamsite.com/Teach/Excel_GP_Code.htm
Once made - use this custom function to return the Cell's formula:
Function CellFormula(Rng As Range) As String
CellFormula = Rng.Formula
End Function
Then enter this on a blank cell in your worksheet:
=CellFormula(B5)
and you will get the resulting formula from B5 rather than its value.
Then I suggest you use a conditional and check to see if "SUM" or (whatever the formula you are trying to check for) is in the string - if it is then color it.
or even just check to see if the CellFormula result is not a number
I.e. use ISNONTEXT or ISNUMBER functions
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$Report Abuse
