excel - 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 InsertNameDefine... 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.