Conditional formating

[Matti]

Hi,

Hope someone can help here - without having to use VB or Macro is it possible to pre-define a different format to cells that contain formulas to distinguish them from cells the contain manually entered values? (for example :each time I enter a formula the font colour will automatically be red and if a value is entered font colour will be black)

Many tks

 

[Nimrod]

Hi Matti:

Concerning the re-calculation of cells:

Once the formulas are in place just press "F9" this will cause all formulas to recalculate.

 

[Nimrod]

Hi Matti:
RE: conditional format base on LOCKED cell

If you are going to have the formula cells locked then there is another way to change the text color without a macro.
You can do a "conditional format" based on if a cell is locked or not !

1-Go into Format ... conditional format..
2-choose "formula is" from drop down
3-type in this formual : =CELL("protect")=0
4-make your format choices
5-select add button
6-type in this formula: =CELL("protect")=1
7-make your format choices

Done : now you have a special format for lock=1, and unlocked = 0

 

[Barry Katcher]

I agree with Nimrod that locking cells with formulas is the way to go - prevents users from overwriting them. I also think Brian from Maui has the right idea - changing the format of unprotected cells so that the user knows which cells he/she/it may enter to.

Chris Davison just helped me last week with some code to emulate Lotus' display of unpretected cells with blue font and protected with black. You run the code whenever you open a new workbook and it goes to the first, second and third sheet of the workbook, locks all the cells in each and, when you un-lock any cells, converts the font in those cells to blue. This code could be modified to show all unproted cells with, say, light yellow background (light yellow won't show on a printout), even more helpful to users, since the blue font only shows after you enter something to the un-locked cells.

Let me know if you want this code and I'll paste it onto a reply.

_________________
...and I always put the seat down.
This message was edited by Barry Katcher on 2002-05-08 14:16

 

[Matti]

Guys,

I'm more than satisfied with your answers and many tks again.

Another challenge though - in this same workbook I would like to have a pop up box or some sort of animation to tell the user that as a result of his input certain cell/cells has/have invalid value. This animation should remain until cells content is rectified. The animation should be visible in any spreadsheet within this workbook. Am I asking too much?

 

[Barry Katcher]

Don't know about hula dancers and dancing elephants, but why not use Data/Validation. This will prevent any invalid data from being entered in the first place, and you can use both a prompt box and an invalid data entry box to alert the users of their boo-boo.

 

[Matti]

Tks Barry,

I'm well aware of the data validation function however it won't do. I'll give an example - in this spread sheet I have among the rest a balance sheet. I need to alert the user if the balance sheet doesn't balance. Now that can happen for a variety of reasons that can not be defined in data validation. I could happen as a result of a row being deleted by mistake in some other spread sheet with in the same work book.

Do you see what I mean? It will have to be a VBA code of some sort.

Tks again,

Matti.

 

[Nimrod]

Hi Matti:
First of all you may want to use cell protection for such things as rows can't be deleted .
Second... please give me a specific example of what you would test for that would show the Balance sheet does not balance. If you give me that I will try to help.

P.S. When you have a new problem you may want to start a new Thread of discussion to increase your responses.

 

[Matti]

Hi Nimrod,

It's quite straightforward - As the workbook contains many spread sheets I've created check cells (the balance sheet check cells for example will get a value <> to zero or #ref# if something goes wrong). Now like I mentioned things can go wrong due to structural changes made in other parts of the spread sheet and I have to highlight the fact that the check cells indicate that something has gone wrong. So roughly speaking an if statement will be - if cell x <> 0 or if #ref# appears then give me a pop up box or something that will flesh so long that this problem is not rectified.

I can’t lock anything as yet as this file is still dynamic and changes are made to it on a daily basis.

I hope I’m clear but let me know if there’s anything you still don’t understand.

Many tks,

 

[Barry Katcher]

This is how I do it on my spreadsheets: Whenever I crossfoot a spreadsheet, for the grand total at the bottom right I put in the formula:

=IF(sum(F1:F100)=sum(A101:E101),sum(F1:F100),"Error, you fool")

If I made an error somewhere, forgot a formula, etc., and the sum of the last column doesn't match the sum of the bottom row, I get the "Error, you fool" instead of the correct total. If you wrap text in that cell, a long message will simulate a drop-down box.

This doesn't tell me what the error is but it does alert me that there is one so I can investigate. If this is sufficient, you can alter the sum formulas in the first part to whatever suit your needs.

_________________
...and I always put the seat down.
This message was edited by Barry Katcher on 2002-05-09 12:24

 

[Yogi Anand]

If you wrap text in that cell, a long message will simulate a drop-down box.

That is neat Barry Katcher!