Auto change of cell colour, depending on date and/or text

[Redbridge Hill]

Hi everyone, 1st post, so here goes!

I would like a cell to change colour depending on the date and/or depending on what character is in the cell.

IE: The cell is coloured white. The date for that cell 01 April 03. When the worksheet is opened, that cells' date (01 April 03) is compared to the current date. If the current date is more than 6 months beyond the cell date ( 01 April 03) then the cell changes colour to red.

Phew, that's the first bit. I hope you're still with me

Next, the cell will have some text characters within it. These will be either single or multiple "b" and/or one "c". If "c" is in the cell then the cell colour is to change to green (regardless of any other text characters). So, if the cell was red due to more than 6 months passing from its' initial date, if "c" was entered, it would go green.

So the cell could contain this for example : bbbc OR c OR bc OR b OR nothing, it will only ever have one "c" in it, but may have no, one or some "b" in it.

Wow, I hope you clever ladies and gents out there can help me, as I am struggling with this.

Much appreciated, RH.

 

[Airwolf]

Re: Auto change of cell colour, depending on date and/or tex

Hi RH

Have a look at Conditional Formatting - under the format menu.

you can test for 'less than =today()-180' for instance. For multiple criteria make sure you put the overiding one as the earliest conditon ie if cell content of b/c cancels the date being more than 6 mnths then it shoul be condition 1 as they are tested in order and applied to the first condition met.

It will say it only can be used for cells on the same 'sheet' but you can get round this by using Range Names if need be.

Good Luck

 

[Redbridge Hill]

Re: Auto change of cell colour, depending on date and/or tex

Ok, I got the colour to change depending on if just a letter "c" is in the cell. How can I make it so that the cell changes colour if "c" is in the cell as well as any other letters (any amount of characters)?

ie c would result in a green cell
wc would result in a green cell
wwc would result in a green cell and so on......

If I can tackle this bit I'll try the date thing later.

Also, does the conditional formatting only allow 3 different conditions or can i create more?

Thanks so far, RH.