change cell color without copying value

March 4, 2011 at 07:17:41
Specs: Windows 7
I need to change cell color on sheet2!b1 when there is a date put into cell a1 sheet 1 (any date) without copyingthe value to sheet2!b1. I tried conditional formatting, doesn't seem to work for me. Could be formula, macro, or vb code.


See More: change cell color without copying value

Report •

March 4, 2011 at 12:16:59
You need to use a Defined Name

On Sheet 1
Select cell A1

On the Ribbon
Select Formula Tab
Select Define Name

Name: MyDate
Scope: Workbook
Refers To: =Sheet1!$A$1

On Sheet 2

1) Select your cell - B1

2) On the ribbon click Conditional Formatting

3) Click on New Rules, it’s near the bottom of the dialog box.

4) Click Use Formula to determine which cells to format.

5) Enter the formula: =IF(MyDate<>"",TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select a pretty color

9) Click OK

10) Click OK

Now as long as cell A1 on Sheet 1 is not Blank
cell B1 on Sheet 2 should display your selected color.


Report •

March 4, 2011 at 14:27:46
Thanks, this works fine with one exception, this is going to be a shared workbook and there will be people hitting the space bar to delete the value in the cell versus right clicking and clearing contents. I need the cell to revert back to no color if the value gets removed from a1.

Report •

March 4, 2011 at 14:41:13
Also, to clarify this will apply to multiple cell say a1:a200, each date in sheet1 a1 needs to correspond to a cell on sheet 2. example; Sheet1!a1 has a date Sheet2!b2 will get colored. Sheet1!a2 has date sheet2!b2 will get colored. If date gets removed cell is have no color.


Report •

Related Solutions

March 4, 2011 at 14:46:46
Sheet 1 Cell A1 must be formatted as a DATE

Then try this formula in your Conditional Formatting:



Report •

March 4, 2011 at 14:53:02
Thanks Mike this is what i am looking for. Now how do i get it to work for lets say a1 thru a 50.


Report •

March 4, 2011 at 15:52:02
I'm not sure if it can be done for a Range of cells,
where sheet!A1 = sheet2!A1 and sheet1!B1 = sheet2!B1

I'll think about it.

You may need VBA code.


Report •

Ask Question