SOLVED

Conditional Formatting, comparing a cell with a formula to another cell in another sheet across row

Copper Contributor

Hello,

 

I am running into a problem here and I can't figure it out, despite googling for hours.

 

I am trying to compare a cell to a cell in another sheet. Lets say I am comparing A1 in sheet_1 to A1 in sheet_2. Using a >= then fill the cell in sheet_1 red. I now want to do the same for cell A2 in sheet_1. IE: compare A2 in sheet_1 to A2 in sheet_2. However, when I drag the cell across the row, all the cells will be compared to cell A1 in sheet_2. I hope that makes sense. I would appreciate any help.

4 Replies
best response confirmed by AndersChristmann (Copper Contributor)
Solution

@AndersChristmann 

Select the cells in column A on sheet_1 that you want to format.

I'll assume that A1 is the active cell in the selection.

On the Home tab of the ribbon, select Conditional Formatting > Manage Rules...

Delete the current rule if there is one.

Click New Rule...

Select 'Format only cells that contain'.

Leave the first drop-down set to 'Cell Value'.

Select 'greater than or equal to' from the second drop-down.

In the box next to it, enter the formula ='sheet_2'!A1 replacing sheet_2 with the real name of that sheet.

Please note that there are no $ signs in the cell reference A1.

Click Format...

Activate the Fill tab.

Select red.

Click OK, then click OK again.

@Hans Vogelaar Thanks for your answer, however, the problem still persists. Even with removing the $ signs from the cell reference to A1 in sheet_2, it will still only reference A1 and not A2 then A3 then A4, and so on.

@AndersChristmann 

Check out the attached workbook - it works there!

You are right. It does work. I just think Excel does a bad job of displaying the function. Either way, thanks for your help. :)
1 best response

Accepted Solutions
best response confirmed by AndersChristmann (Copper Contributor)
Solution

@AndersChristmann 

Select the cells in column A on sheet_1 that you want to format.

I'll assume that A1 is the active cell in the selection.

On the Home tab of the ribbon, select Conditional Formatting > Manage Rules...

Delete the current rule if there is one.

Click New Rule...

Select 'Format only cells that contain'.

Leave the first drop-down set to 'Cell Value'.

Select 'greater than or equal to' from the second drop-down.

In the box next to it, enter the formula ='sheet_2'!A1 replacing sheet_2 with the real name of that sheet.

Please note that there are no $ signs in the cell reference A1.

Click Format...

Activate the Fill tab.

Select red.

Click OK, then click OK again.

View solution in original post