Forum Discussion
Solved
Conditional Formatting, comparing a cell with a formula to another cell in another sheet across row
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.
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.
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.
HansVogelaar 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.
Check out the attached workbook - it works there!
