Jan 18 2023 02:06 AM
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.
Jan 18 2023 02:36 AM
SolutionSelect 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.
Jan 18 2023 03:45 AM
@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.
Jan 18 2023 04:06 AM
Check out the attached workbook - it works there!
Jan 18 2023 04:24 AM
Jan 18 2023 02:36 AM
SolutionSelect 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.