Apply conditional format color to each cell in a row relative to the value of each cell in a row from another sheet

[tmk0427]

Hi excel experts, please help me with a formula that will apply a color in each cell who's value is greater than or less than each associated cell's value in the same row of a duplicate sheet. For example, tab 1 cells A1-E1 each have a value such as either 1 or 2. In tab 2 same cells A1-E1 if there is a variance in the value of these cells then Tab 1 cells A1-E1 will fill with a red color. I want to be able to apply this format to all cells in multiple rows on tab 1 say down to A1 to E20 that are based on the values of tab 2 same A1-E20 as an example. In reality I would like to apply this format to as many cell ranges I want on tab 1 that are the same as tab 2 range of cells. Hope this makes sense. Thanks.

 

[Fluff]

Select the entire range and use this formula

=A1<>Sheet1!A1

 

[tmk0427]

So I selected a range A1-E1 on sheet 1 and applied that formula in the conditional formatting option and selected a red fill color as the format but when I enter a 1 in any cells A1-E1 on sheet 2 there is no change in fill color in any cell A1-E1 on sheet 1. Am I doing something wrong? Also, I want to apply a different fill color if the values in cells A1-E1 are less than the same cell values in sheet 2 A1-E1 cells and I want to apply a different fill color if the cell values in sheet 1 are greater than cells in sheet 2. Thanks.

 

[LegendaryAxe]

So I selected a range A1-E1 on sheet 1 and applied that formula in the conditional formatting option and selected a red fill color as the format but when I enter a 1 in any cells A1-E1 on sheet 2 there is no change in fill color in any cell A1-E1 on sheet 1. Am I doing something wrong? Also, I want to apply a different fill color if the values in cells A1-E1 are less than the same cell values in sheet 2 A1-E1 cells and I want to apply a different fill color if the cell values in sheet 1 are greater than cells in sheet 2. Thanks.

Change the formula to "Sheet2" instead of "Sheet1".

 

[tmk0427]

Thanks

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[tmk0427]

Sorry one more question. What would the formula be if I did not want the cell to have a fill color if the sheet 2 data cells are blank? Thanks.

 

[LegendaryAxe]

Try

=AND(A1<>Sheet2!A1,Sheet2!A1<>"")

 

[tmk0427]

Fantastic. Thanks again.

 

[LegendaryAxe]

Fantastic. Thanks again.

No problem!