color cells

TBorland

Board Regular
Joined
Dec 3, 2011
Messages
209
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
I don't believe formulas can change the cell color.

You will probably have to use a macro.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Thanks Mike

I selected Sheet2 A1

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

Thanks
Tony
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,792
Messages
6,104,000
Members
447,890
Latest member
CADim_JParker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top