Excel - Change a cell color issue

May 13, 2009 at 10:38:00
Specs: Windows XP
I want to change the fill a cell (A1) on one worksheet to blue if a group of cells (B1 to B10) on a separate worksheet has no blanks. Basically, IF B1:B10 is not blank, change A1 to blue.

Is there a formula to do this? I couldn't see how conditional formatting would work in this case given it would need to span two worksheets and the "IF" part references multiple cells.



See More: Excel - Change a cell color issue

Report •

May 13, 2009 at 11:51:15
You can use a “defined name”

On Sheet 2, Column B, Cells 1 thru 10 input any character into each cell.

Highlight Column B, Cells 1 thru 10

On the Task Bar
Select Insert
Select Name
Select Define

Type the name “mylist” (without the quotes)

If you look at the bottom of the window, you will see that it should refer to the highlighted cells B1 thru B10 and should have the formula:


Click OK

On Sheet 1, Column A, Cell 1

On the Task Bar
Select Format
Select Conditional Formatting
Change “Cell Value Is” to “Formula Is”
Enter the formula:


Select Format
Select Pattern Tab
Select a pretty color
Press OK
Press OK

Cell A1 should now display your pretty color.

Go to Sheet 2 and delete a character from the B1 thru B10 list and when you return to Sheet 1
Cell A1 will now be blank.



Report •

May 13, 2009 at 12:02:29
Try naming your range and using this as your CF formula:


Report •

May 13, 2009 at 12:10:09
An alternate (easier?) way to create a Named Range is to select your range then click in the box above Column A, enter your Range_Name and hit Enter.

Make sure you hit Enter. If you type in the name and then click outside the box, the range name will not take effect.

Report •

Related Solutions

Ask Question