Changing colours depending upon values

[WelshyWizard]

Hi all,

I've got two tables. One on worksheet 'Front' and one on worksheet 'Workings'

On the 'Front' sheet I have a series of dates (Filling cells A530, although some cells may be blank). Ont he 'Working' sheet I calculate the number of working days between the date on the front sheet and today i.e. =NETWORKDAYS(Front!D6,TODAY())-1.

So for example, if the value in cell A5 on the 'Front' sheet is 13/03/2007, the value in A5 on the 'Working' sheet is 3.

What I need to do is colour code the date on the 'Front' sheet depending upon the value in the corresponding cell on the 'Working' sheet. For example 1 to 3 = Green, 4 & 5 = Orange, >5 = Red.

How can I do this? The only way I could think of was to flip between the two using a loop.

Can anyone help?

Cheers.

Today is the tomorrow you worried about yesterday - and all is well.....

 

[xlbo]

Conditional Formatting

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[WelshyWizard]

Thanks Geoff,

I've written a small loop which works quite well running through some if statements.

Does anybody know how I stop the =NETWORKDAYS(Front!D6,TODAY())-1 formula from returning #Value! if the value on the 'Front' sheet is not a date, or the figure 27969 if the cell is blank on sheet 'front'.

Cheers

Today is the tomorrow you worried about yesterday - and all is well.....

 

[SkipVought]

Hi,

Use the IsError function

Skip,

_[red][/red]