Show a value when a cell is specifc colour

[SYCAMORE]

I have a speadsheet where i show a roto of individuals when they are at work.
For the duration they are at work ( 14 days) their rota is highlighted a colour. ( green) along a Row.
When a cell is coloured green i would like it to show a single digit (1) therfore making the number of individuals working easy to add up as digits when you look at the column total, as some will overlap with other rows and cells that are coloured.

How can i do this?

Thanks Mike

 

[mvptomlinson]

Rather than adding a 1 to all cells that are green, why not use conditional formatting and start typing a 1 into cells that represent working hours?

Your way, you would need a macro. Using C.F., you could format all cells in a region so that if they contain the value 1 (or any number/text if you want) to appear green and even use green font so you don't actually see the value.

 

[SYCAMORE]

Thanks for the reply , I am working with Excel 2003 here...

 

[mvptomlinson]

You can still use conditional formatting. It's been around for a long time.

http://www.contextures.com/xlcondFormat01.html