I have a sheet displaying some water quality values, and for most of the cells I have the numbers displaying a calculated value. But once in a while I actually measure the value, and type in the measured, rather than calculated, number in the cell. I'd like the cells to automatically display the number as black when it is pointing to the calculation, but red when I type the number in. How can I do this?
Thanks!
Sean
Last edited by AliGW; 02-12-2018 at 06:52 AM.
Use conditional formatting on the range.
Assuming that A1 is the start of the range where the CF rule needs applying:
=ISFORMULA(A1)=FALSE
Set this to red text.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Sorry - just realised you only have 2003, so this won't work. You will need VBA to do it.
Hi AliGW,
I do have conditional formatting on my version of excel. Are you sure it shouldn't work?
It's the function (ISFORMULA) that won't work - sorry. It's a relatively new addition to the software.
Hmm.. So do you know how I should go about doing it then?
No, sorry - I don't do VBA. I'll move your thread to that section to attract the attention of someone who can help.
Thanks a lot!
This is another possibility without VBA that I hope it works in Excel 20013
Try this formula:
=ESERROR(ENCONTRAR("=";FORMULATEXTO(A1);1))
In English probably is
=ISERROR(FIND("=";FORMULATEXT(A1);1))
No, that won't work, either. You can easily check online by Googling the function name:
https://support.office.com/en-us/art...e-09cda35439c8
Sorry, I was not aware that FormulaText function did not work in Excel 2003
In that case try adding a module to your workbook with this code:
Then, you can use the function in a cell this way:
Please Login or Register to view this content.
=IsFormula(A1)
The function needs to be used in a conditional formatting rule - will that work?
I believe this UDF will work.
Please Login or Register to view this content.
Last edited by yujin; 02-12-2018 at 08:39 AM.
Thanks for the replies. Could someone give me a bit of a walkthrough as to how to input these formulas into excel? I'm still a bit of a newbie with this stuff. Thanks!
Do you know how to use Conditional Formatting?
If no, there're a bunch of resources on the web, so just google it with the keywords "excel 2003 coditional formatting".
UDF stands for User Defined Function, that is a custom function created by using VBA code.
The UDF below returns TRUE if the target cell contains Formula.
First of all, to use this UDF, open VBA editor by hitting ALT+F11.Please Login or Register to view this content.
Next, insert a Standard Module (ALT+I, M) and paste the code.
Now you can use the UDF just like pre-defined worksheet functions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks