in 2007 excel sheet i am trying to change cell colour by inserting a letter in the cell below so when i insert the letter the cell above changes colour

can someone show me how to do this

Thanks

Mark

Hi, Try this:

Assuming cell A1 is the cell to change color and cell A2 is the cell to contain a letter.

Select cell A1

From the Ribbon select Home - Styles - Conditional Formatting,

From the drop down select 'Manage Rules' and select 'New Rule'

In the dialog box select the last item in the list 'Use a formula to determine which cells to format'

Enter this formula in the box=IF(A2="A",TRUE,FALSE)

Note that TRUE and FALSE do not have double quotes - they are Excel's logical values true and false.

Click the format button and from the 'Fill' Tab select a color, click OK

Click OK and select 'New Rule'

Select 'Use a formula to determine which cells to format'

Enter this formula in the box=IF(A2="B",TRUE,FALSE)

Click the format button and from the 'Fill' Tab select a second color, click OK

'Applies to' will show the selected range, and the 'Stop if True' boxes are checked.

Click Apply and OK

Repeat for as many colors as required.You don't need to have multiple colors, for A1 to change color if there is anything in cell A2 use

IF(A2<>"",TRUE,FALSE)Regards

Hi I have tried that and works a treat

many thanks

Mark

You're Welcome Regards

Humar

Hi i have tried this but wanted it to cover a selected area of cells, can this be done without doing this for every cell

Thanks

Hi, Yes, you should be able to select a range of cells and add the formula once and it will apply to all the cells.

I do not have Excel 2007 on this PC, so I can't test it now, but I think that when you get to the step showing the formula that there is a box showing the range of cells that the formula/formatting applies to.If you wanted cells A2 to E2 to respond to A1 to E1:

select cell A2 first, then hold down the shift key and extend the selection to E2

Then follow the instructions for adding the formula

Use =IF(A1 as the start of the formulaIf you wanted cells A2 to E2 to all respond to A1:

select cell A2 first, then hold down the shift key and extend the selection to E2

Then follow the instructions for adding the formula

Use =IF($A$1 as the start of the formulaHope one of these does what you want.

Basically when you have a selection, Excel takes what you entered as the formula etc. for the first cell selected in the selected area and then extends it to all the cells in the selection.If a cell address in the formula has $ signs in it, the address

is notchanged across the selection. If there are no $ signs, the addressischanged across the selection.If you wanted -

cells A2, A3 and A4 to respond to A1, and

cells B2, B3 and B4 to respond to B1,

you would select A2, then extend the selection to B4

and enter the formula starting like this: =IF(A$1

cells.Regards

Hi, this worked thanks

Mark

Ask Your Question

Weekly Poll