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

User · Accepted Answer

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   Go to Sheet1  Insert a new column by right-clicking column A s header and selecting  Insert   Enter the following formula into A1    IF ISBLANK Sheet2 B1     Sheet2 B1  Copy A1 by right-clicking it and selecting  Copy   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   Stay on Sheet1  Select column B by left-clicking its header  Select the menu item Format   Conditional Formatting    Change Condition 1 to  Formula is  and enter this formula    MATCH B1  A  A 0  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   Stay on Sheet1  Right-click the header on column A and select  Hide     This should automatically update Sheet1 whenever anything in Sheet2 is changed

User · Answer

You can also do this with named ranges so you don t have to copy the cells from Sheet1 to Sheet2    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  Define a named range  say Sheet2Vals for the column that has the values you want to conditionally format  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can also do this with named ranges so you don t have to copy the cells from Sheet1 to Sheet2    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  Define a named range  say Sheet2Vals for the column that has the values you want to conditionally format  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

User · Answer

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

User · Answer

Here is my own solution for restoring the original colors when copying certain highly formatted sheets or templates to a new spreadsheet   it copies all data directly  so it only works if you need to copy a sheet  not only apply colors to a different sheet with different data   copy the original format workbook by ctrl   g and selecting the appropriate range  paste it into the new work sheet  colors will be all changed  with the destination still highlighted  right click and go to  Paste special  and select  All using source theme   then repeat the paste special  only with  Values  this time  and it should be identical to the original sheet you copied

User · Answer

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

User · Answer

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

User · Answer

You can also do this with named ranges so you don t have to copy the cells from Sheet1 to Sheet2    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  Define a named range  say Sheet2Vals for the column that has the values you want to conditionally format  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

User · Answer

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

User · Answer

Here is my own solution for restoring the original colors when copying certain highly formatted sheets or templates to a new spreadsheet   it copies all data directly  so it only works if you need to copy a sheet  not only apply colors to a different sheet with different data   copy the original format workbook by ctrl   g and selecting the appropriate range  paste it into the new work sheet  colors will be all changed  with the destination still highlighted  right click and go to  Paste special  and select  All using source theme   then repeat the paste special  only with  Values  this time  and it should be identical to the original sheet you copied

User · Answer

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

User · Answer

You can also do this with named ranges so you don t have to copy the cells from Sheet1 to Sheet2    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  Define a named range  say Sheet2Vals for the column that has the values you want to conditionally format  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

[excel] Format cell color based on value in another sheet and cell

Creating a mirror of Sheet2, column B in Sheet 1

Applying the conditional formatting

Hiding the mirror column

Examples related to excel