color cells

[TBorland]

I have 2 sheets.The first sheet has a long list of names and there tasks.If they are compentent with a task i make it a "A" in the cell using a drop down list to select.If they are training i make it a "B" in the cell and finally if nothing is happening for a task i want the cell to be empty.
My questions are:

1. If sheet 1 has a "A" in cell C15 i want sheet 2 to fill the cell as color Green in C15
2. If sheet 1 has a "B" in cell C15 i want sheet 2 to fill the cell as color Blue in C15
3. If sheet 1 is EMPTY in cell C15 i want sheet 2 to leave the cell blankin C15

What formula would i need to place in sheet 2 Cell C15 so to display the correct color depending on sheet 1 entry....would IF statement be the best option?

Cheers

Tony

 

[delaneyjm]

I don't believe formulas can change the cell color.

You will probably have to use a macro.

 

[Dannyh1]

You can change colours via Conditional Formatting but only within same sheet. As delaneyjm says, it would have to be code.

Is this an option?

 

[TBorland]

Hi, thanks for the replies

Code would be good.Will the code also work for future entries of names into sheet 1

Thanks again....using Excel 2010

Tony

 

[mikerickson]

You can use Conditional formatting across sheets by using names
Select Sheet2 A1
Define the name Name: Sheet1Cell RefersTo: =Sheet1!A1
(note the Sheet1 and relative referencing)

The putting the CF condition =(Sheet1Cell="A") in Sheet2!C15 will work as expected.

 

[TBorland]

Thanks Mike

I selected Sheet2 A1

Where abouts do i exucute the "Define the name Name: Sheet1Cell RefersTo: =Sheet1!A1"

Thanks
Tony

 

[mikerickson]

I don't know where the Define Name command is on all versions of Excel. I think its on the Formula ribbon in most versions. Excel Help will know for your version.

 

[TBorland]

Hi Mike

All done but how do i have the cells in sheet2 filled with color Green for "A" (from sheet1), color Blue for "B" (from sheet1) and Empty for all other entries.I get True for "A" , False for "B" and False for all other entries.

Thanks

Tony

 

[mikerickson]

The color can be done with multiple conditions of CF.
The formula =IF(OR(Sheet1!C15="A",Sheet1!C15="B"), Sheet1!C15,"") should empty it.

 

[TBorland]

Thanks Mike