PDA

View Full Version : Solved: Automatic Cell formating



excelliot
01-09-2007, 01:52 AM
:dunno Pl check attached sheet, can anybody help me out to find how border to cell & cell color is chenged to yellow when data is changed in no. of payments per month. :dunno

Simon Lloyd
01-09-2007, 02:05 AM
Elliot most of your calculations are done using defined names for the formulae, you can view these by clicking Insert | Names | Define from the menu bar, some of your defined names also have defined names in the formulae, it's just a matter of back tracking and looking at each part of the formula.

Other than this i didnt understand your question!

Regards,
Simon

Charlize
01-09-2007, 02:51 AM
If you remove the sheetprotection you can select a cell with the yellow format. Select the option Format - Conditional Format. Normally you will see now how the cells are formatted when the condition is met.

The row is formatted based on the value of the named range 'last_row'. The problem is that I am not able to locate the range 'last_row'. Is it a calculated value : number of years * number of payments per year ?

After some digging in the setup of your sheet I found this one :

last_row = IF(Values_Entered;Header_Row+Number_of_Payments;Header_Row)

Choose 'Insert - Name - Define' in the menu on top. When you scroll through the list you will find the value that is used to color the row when there is data in it.

Charlize

excelliot
01-09-2007, 10:49 PM
Thanks alot to both of you, i have got my answer.