Change text colour based on list option chosen

[supergirl21]

Hi everyone,

I am working on a spreadsheet for financial data which is based on forecast and actual data. As it's the start of our financial year we don't have any actual data yet so all figures in all the 'actual' rows match the dummy forecast data.

Dummy sheet.xls

What I'm wondering is if there's a way to do the following:

If I choose 'actual' from the list at C3, D3, etc instead of 'forecast', is there a way to set it up so that it changes the text colour (or fill colour) of the corresponding actual data at C9, C15, C21, C27 and C33? ie. so that it's clear by colour when data is 'actual' and when it is 'forecast'?

FYI I'm using Excel 2007 but a lot of others use 2003 so it needs to be as compatible as possible.

Any help is greatly appreciated!

[Harribone]

Follow these steps:

Select C9.
On the Home Tab click Conditional Formatting and choose New Rule.
Select 'Use a formula to determine..' and for the formula below type in =C$3="Actual"
Click the Format button and apply whatever you want to do to the cell (colour change etc.) and click OK to confirm your changes.

Check this is working and if it is:
With C9 selected click the Format Painter button. Then select all the cells to the right of this and you have set up that row to change colour.

Repeat this painter step for each row. If you are having trouble with anything let us know.

[supergirl21]

Follow these steps:

Select C9.
On the Home Tab click Conditional Formatting and choose New Rule.
Select 'Use a formula to determine..' and for the formula below type in =C$3="Actual"
Click the Format button and apply whatever you want to do to the cell (colour change etc.) and click OK to confirm your changes.

Check this is working and if it is:
With C9 selected click the Format Painter button. Then select all the cells to the right of this and you have set up that row to change colour.

Repeat this painter step for each row. If you are having trouble with anything let us know.

Thanks so much, Harribone! Your instructions were great and really easy to follow. However, I don't want the whole row to colour when I choose Actual at C9. Essentially each month we will change that month from Forecast to Actual.

Is the only way to format the colour change to do each individual cell of each month and each row separately? I can do that (of course) but is there a more efficient way so that when I choose 'Actual' for July, it changes the format for ALL the cells in that column labelled expense actual, revenue actual, etc (ie. C9, C15, C21, C27, C33)?

[mikerickson]

Duplicate idea

[Harribone]

When you copy this across the row it will apply the formatting rule but each one will only change when the top cell is set to actual (the formula changes from =C$3 to =D$3, =E$3 automatically).

You could use the format painter and select the whole range to applay formatting to all the cells in one go for quickness but it you do that gap rows you have will end up changing colour too.