Format cell color based on value in another sheet and cell

Question

I have a workbook with two sheets. I would like to format the cell background color in the first column of sheet 1 based on the values in the second column of sheet 2.

For example, if the value of of sheet2, row 6, column 2 is say, 4, then I would like the background color of sheet 1, row 4, column 1 to be green. If none of the values in sheet 2, column 2 reference a particlar row in sheet 1, I'd like to leave it set to no color. There's no prohibition against the same value appearing multiple times in the second column of sheet 2. Bonus kudos if you can tell me how to unset the color if the last value in sheet 2 pointing to a row in sheet 1 is removed.

I'm sure that for the Excel wizards out there this could be trivial, but I rarely have occasion to use excel and certainly don't have time to become a black belt in it. Can anyone offer me advice, pointers, or a quick formula to do this? If this is going to take some complex VB code to implement, it isn't worth it.

Thank you!

Answer 1

You can also do this with named ranges so you don't have to copy the cells from Sheet1 to Sheet2:

1. Define a named range, say Sheet1Vals for the column that has the values on which you want to base your condition. You can define a new named range by using the Insert\Name\Define... menu item. Type in your name, then use the cell browser in the Refers to box to select the cells you want in the range. If the range will change over time (add or remove rows) you can use this formula instead of selecting the cells explicitly:

   =OFFSET('SheetName'!$COL$ROW,0,0,COUNTA('SheetName'!$COL:$COL)).

   Add a -1 before the last ) if the column has a header row.

2. Define a named range, say Sheet2Vals for the column that has the values you want to conditionally format.

3. Use the Conditional Formatting dialog to create your conditions. Specify Formula Is in the dropdown, then put this for the formula:

   =INDEX(Sheet1Vals, MATCH([FirstCellInRange],Sheet2Vals))=[Condition]

   where [FirstCellInRange] is the address of the cell you want to format and [Condition] is the value your checking.

For example, if my conditions in Sheet1 have the values of 1, 2 and 3 and the column I'm formatting is column B in Sheet2 then my conditional formats would be something like:

=INDEX(Sheet1Vals, MATCH(B1,Sheet2Vals))=1
=INDEX(Sheet1Vals, MATCH(B1,Sheet2Vals))=2
=INDEX(Sheet1Vals, MATCH(B1,Sheet2Vals))=3

You can then use the format painter to copy these formats to the rest of the cells.

Answer 2

Here's how I did it in Excel 2003 using conditional formatting.

To apply conditional formatting to Sheet1 using values from Sheet2, you need to mirror the values into Sheet1.

Creating a mirror of Sheet2, column B in Sheet 1

1. Go to Sheet1.
2. Insert a new column by right-clicking column A's header and selecting "Insert".

3. Enter the following formula into A1:

   =IF(ISBLANK(Sheet2!B1),"",Sheet2!B1)

4. Copy A1 by right-clicking it and selecting "Copy".
5. Paste the formula into column A by right-clicking its header and selecting "Paste".

Sheet1, column A should now exactly mirror the values in Sheet2, column B.

(Note: if you don't like it in column A, it works just as well to have it in column Z or anywhere else.)

Applying the conditional formatting

1. Stay on Sheet1.
2. Select column B by left-clicking its header.
3. Select the menu item Format > Conditional Formatting...

4. Change Condition 1 to "Formula is" and enter this formula:

   =MATCH(B1,$A:$A,0)

5. Click the Format... button and select a green background.

You should now see the green background applied to the matching cells in Sheet1.

Hiding the mirror column

1. Stay on Sheet1.
2. Right-click the header on column A and select "Hide".

This should automatically update Sheet1 whenever anything in Sheet2 is changed.