Change cell/ font color if a value is manually entered

Hi katie1071,

Is this what you are After :-
Format
Conditional Formatting
Cell Value Is
Greater Than 0 ( Zero )
Set your Format

Hope this Helps.
All the Best.
SHADO

Sorry, that didn't work...the cells have VLOOKUP formulas, which will return text in some columns and numbers in others. Because when I look at a spreadsheet that someone else has filled in, I can't determine if the person has manually typed in a value unless I click on each cell to see if the formula has been deleted. If I were able to set the particular cells to change font or cell color when this is done, I can easily see where they've removed the formulas.

hi katie,
i think SHADO gave the right instructions...if you highlight the entire column/row that needs checking, then go to Format, Conitional Formatting. select in the first option box "formula is"...then type your formula in the second box, and format to whatever you like, it should highlight all cells with that formula present, and leave manually altered cells alone. its a backwards way of doing what you wanted, but i think that would work?

I feel like a bit of a blonde at the moment , but that doesn't work either.

I do not have a lot of experience with conditional formatting unfortunately. Cell is greater than zero changes the format of all cells regardless if they are formulated or manually entered. And, it won't allow me to enter a vlookup formula in conditional formatting.

Any other suggestions?

katie1071 said:

I feel like a bit of a blonde at the moment , but that doesn't work either.

Click to expand...

That's because that suggestion would not have worked regardless of anyone's hair color.

It sounds like what you want to do is visually recognize cells that contain formulas from cells that contain constants (i.e. something manually entered).

Try this, it worked for me, and assumes you truly want cells containing formulas in one color, and cells containing text another color, which means cells containing neither will have no color.

Note, if you do not want to color the formula-containing cells, then in Step 8 below, instead of picking a color from the color palette, select the "No color" option in that dialog above the palette.

The brunt of the CF for formulas was identified by David Hager, so thanks to him. Follow these steps:

Step 1
Click on Insert > Name > Define

Step 2
In the "Names in workbook" field, enter
"Formulas" without the quotes. Actually you can enter most any name you want, but let's keep it simple and call it Formulas.

Step 3
In the "Refers to" field near the bottom, enter
=GET.CELL(48,INDIRECT("rc",0))

Step 4
Click Add, then click OK.

Step 5
Select the range of cells on your worksheet that you want to conditionally format for formulas, for this example beginning in cell A1, so A1 is the active cell with all other cells you've selected on the sheet.

Step 6
Click on Format > Conditional formatting.

Step 7
Click the drop down arrow and select "Formula is". In the field to the right of that, enter
=Formulas

Step 8
Click on the Format button, select the kind of formatting you want from the options available, maybe a red square from the Patterns palette, and click OK. Click "No color" if you do not want formulas shaded.

Step 9
While still in the Conditional Formatting dialog, click the Add button.

Step 10
Click the drop down arrow and select "Formula is". In the field to the right of that, enter
=AND(LEN(A1)>0,ISTEXT("rc"))

Step 11
Click on the Format button, select the kind of formatting you want from the options available, maybe a green square from the Patterns palette, and click OK, then OK again to exit the Conditional Formatting dialog.


That should do it. Formula cells are in red (or nothing if you chose "no color" in Step 8), text cells are in green, empty cells have no conditional shading.

In Excel 2000 or before, if you try to copy and paste a cell references of an XLM formula (such as GET.FORMULA) into a different worksheet, that will result in a fatal error (Excel will crash), and you will lose unsaved data. I'm pretty sure this has been rectified with Excel2002 and XP (my system did not crash with Excel2003 and XP), but please be aware of that possibility.

Hi Tom Urtis,

I Misunderstood katie1071's Request.
I have Learnt Something Today.
I will Definately Put your Response in my Goodies File for Future Reference.

Thanks Very Much.
All the Best.
SHADO

Sorry for not being clear Shado...

Thanks for this Tom, it absolutely works! ray:

...the Blonde from the Big D

No Apology Necessary katie1071.
I am Glad Tom's Solution Works for you.

All the Best.
SHADO

Next question...since I have multiple areas throughout the worksheet to apply this conditional formatting to, and this won't work on multiple columns (tried changing =AND(LEN($X$36:$AH$100)>0,ISTEXT("rc")) and only 3 formats can be utilized, is there something else I can do to apply it to multiple areas?