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.

 

[tmk0427]

So sorry to bother you again. But I discovered that when I delete all of sheet 2 cells making them blank and start adding a value in say sheet 2 A1, then all the cells in sheet 1 A1-E1 fill with the red color. I only want the cells in sheet 1 to fill with color when there's a non blank value in any of the sheet 2 cells if they are greater or less than the sheet 1 cell A1-E1 values.

 

[LegendaryAxe]

The new formula should cover instances where the cells are blank. Are you sure the new conditional formatting rule covers all the cells you want it to and the previous conditional formatting rule you applied was deleted?

 

[tmk0427]

The new formula should cover instances where the cells are blank. Are you sure the new conditional formatting rule covers all the cells you want it to and the previous conditional formatting rule you applied was deleted?

Yes. I only applied the new formula. But I did include multiple columns of rows for example I expanded the formula to cover cells A1-10 to E1-10. Sheet 1 has values in it that I want to stay there and sheet 2 cells are the ones I want to change. The cells in sheet 1 that have existing values are all being color filled when I just enter 1 value in sheet 2 A1.

 

[LegendaryAxe]

Can you hit alt O D when you're in Sheet1 of the Excel file and take a print screen of the window that pops up? The window should look something like this:

 

[tmk0427]

Can you hit alt O D when you're in the Excel file and take a print screen of the window that pops up? It should look like this:

I think I found the source of the problem. I took out the $ signs and it worked.

 

[LegendaryAxe]

I think I found the source of the problem. I took out the $ signs and it worked.

Oh yes, absolute references will definitely cause that problem. Glad you figured it out.