Conditional Formatting (?) based upon another cells colour

I hope someone can help me please.

I have a worksheet (Excel 2000) showing a number of holiday properties, their prices and availability.

In the attached screen print, I have included an example where the required stay runs from 03/11/12 to 10/11/12 as shown in cells G1 and G2; the prices for these dates for all available properties are shown in row 24.

For the property shown in column AE, a price is shown (circled in yellow) but for these dates it is actually unavailable which is shown by the red shading (circled in black).

Is there a formula that I could use in conditional formatting please that could change the font and background in cell AE24 (thus showing a blank cell) when any of the days between 03/11/12 and 10/11/12 (in this case cells AE34 to AE40) are coloured red.

Obviously, this would need to work based upon whatever dates I enter in G1 and G2.

I have very little knowledge of VBA and would prefer to use a formula.

Thanks in advance.

WorksheetFullyBooked.PNG

Thanks Fotis

I've tried to attached a sample having deleted all sensitive information but it's comin up as 1.49mb which is above the 1.00mb limit...any suggestions ?

I hope you can help...loving the pictures by the way !

Thanks

Thanks for that, here it is, hope it makes sense !

Best wishes

Simon

PRICELIST2013.xls

Hello

Can anyone help please ?

It took me ages to "strip" the spreadsheet of sensitive data to be able to provide the requested sample.

Thanks

Thanks for the reply.

I've attached a new copy of the sample spreadsheet. OK, I'll explain from the beginning.

As you can see in cells D1, D2 and D3, the dates of the stay and the number of people staying have been entered; this gives a price for the property for that stay of £140 (as shown in cell AE24). However, the red pattern to cells AE27 to AE70 denotes that that property is not available.

Is there a formula that I could use that would return a value of "0" in cell AE24 when any of the days between 03/11/12 and 10/11/12 (in this case cells AE34 to AE40) are coloured red.

Obviously, this would need to work based upon whatever dates I enter in G1 and G2.

I have very little knowledge of VBA and would prefer to use a formula.

Thanks in advance.

PRICELIST2013.xls

Hello Fotis1991

Please accept my sincere apologies for the delay in replying; that is superb, thank you very much.

I've shown my appreciation for your reputation.

Warm Regards