Change cell/ font color if a value is manually entered

[katie1071]

Hi there,

I have a spreadsheet with a ton of vlookups and I want to be able to see when someone has manually entered a value into the cell in specific columns. I'm sure there is a guru out there who can help me out.

Katie

 

[Tom Urtis]

since this won't work on multiple columns

Why not? It's Conditional formatting and we only applied two CFs, one for Formulas and one for Constants. You should be able to apply this to as many columns or ranges you want, unless you have more conditions in mind than you said, and if so what are they? The formula itself makes no difference, the condition is simply that a formula of any kind (or constant or nothing) is present in the cell. You can copy any cell you've applied this CF to already, then select any other range(s) and click Edit > Paste Special for Formats, them hit the Esc key to exit Copy mode. Maybe I am misunderstanding your follow-up question but on the face of it that is how I'd address t.

 

[katie1071]

Oooooh...gotcha! I tried to apply the format to a selection of cells rather than to 1 cell then copying and pasting the format to other cells. That worked. Thanks again!

 

[logixrat]

Tom,

I just wanted to say thanks for posting up your technique for shading of text and formula related cells. I am new to the forum, but someone referred me here and it is exactly the answer I needed. Very cool of you to post such great information.

I might note I am running MS Office 2003 SP1 (11.6355.6360) and Windows XP Pro 5.1.2600 SP2 and do not get a crash error. However when closing and re-opening the document Excel alerted me I had a MS Excel 4.0 macro running and that it would not allow it to run. Clicking OK allows you to enter into the program, but takes away the conditional formatting previously established. And in my case it turned ALL my selected range of cells to turn pale yellow (color selected for text based cells).

The easy fix is to change your macro security to medium which will allow you to choose rather or not to run the MS Excel Macro 4.0. Doing so enabled the document to open w/ all previously established conditional formatting.

I will also note that other features such as bold/italics and font color changes selected in the conditional format box is not valid. I am guessing you would need to modify your code to enable such features, but thought I would point it out for others that might be reading.

Again, thanks for posting this information. It has really helped save the day for me.

 

[CA_User]

text CF does not work for me

Hi..

The tips worked for Katie but when I got to step #11, it does not work.

can soemone please help?

I have excel 2000.

 

[Tom Urtis]

Tom,

I just wanted to say thanks for posting up your technique for shading of text and formula related cells. I am new to the forum, but someone referred me here and it is exactly the answer I needed. Very cool of you to post such great information.

What a nice message, thank you very much for taking the time to post it.

The tips worked for Katie but when I got to step #11, it does not work.

Of all the Steps, 11 should be problem free because it is simply selecting a format option in the CF dialog. Something else is going on, either with what you did or did not do for Steps 1 through 10, or who knows what, but really it all does work. Instead of saying "it does not work", post a more precise description of what is or is not happening, and what exactly you did and what you expect the result to be, so someone reading this can assist.

 

[CA_User]

I figured it out. THANKS!!!!! I had the wrong cell reference in my formula.

 

[CA_User]

tom -

can you recommend some reference material (or book?) that has answers to all types of excel questions.

thanks

 

[Tom Urtis]

Ha !! Don't we all wish there was a one stop shop for all Excel answers !! No such nirvana I'm afraid, but if you keep monitoring these newsgroups and read the popular books and keep practicing with the program, that's the best way to catch on.