Format cell color based on value in another sheet and cell

Question

Howdy,

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

I've done this before with conditional formatting. It's a great way to visually inspect the cells in a workbook and spot the outliers in your data.

Answer 2

I'm using Excel 2003 -

The problem with using conditional formatting here is that you can't reference another worksheet or workbook in your conditions. What you can to do is set some column on sheet 1 equal to the appropriate column on sheet 2 (in your example =Sheet2!B6). I used Column F in my example below. Then you can use conditional formatting. Select the cell at Sheet 1, row , column 1 and then go to the conditional formatting menu. Choose "Formula Is" from the drop down and set the condition to "=$F$6=4". Click on the format button and then choose the Patterns tab. Choose the color you want and you're done.

You can use the format painter tool to apply conditional formatting to other cells, but be aware that by default Excel uses absolute references in the conditions. If you want them to be relative you'll need to remove the dollar signs from the condition.

You can have up to 3 conditions applied to a cell (use the add >> button at the bottom of the Conditional formatting dialog) so if the last row is fixed (for example, you know that it will always be row 10) you can use it as a condition to set the background color to none. Assuming that the last value you care about is in row 10 then (still assuming that you've set column F on sheet1 to the corresponding cells on sheet 2) then set the 1st condition to Formula Is =$F$10="" and the pattern to None. Make it the first condition and it will override any following conflicting statements.

Answer 3

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.

Answer 4

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 5

Dear Friend,

Can someone tell me how to count cell on base of color filled in given range.

i need it urgently and i will be thankful to you all.

Regards

http://www.winxptrick.blogspot.com