Conditional formatting across sheets

Copper Contributor



I'm sure this isn't as hard as I'm making it but can't figure it out. I'm trying to make conditional formatting highlight numbers from "cells B2:B32 on Sheet 3" that are higher than "cells M2:32 on Sheet 1," wherein I'm comparing the same row number to each other (meaning sheet 3, cell B2 needs to be highlighted if it's higher than sheet 1, cell M2 and so forth down the rows). Then there's a few more columns I'll need to do from there but if someone can get me started on how to do that I'd very much appreciate it



1 Reply


Select B2:B32 on Sheet 3.

B2 should be the active cell in the selection.

On the home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'greater than' from the second drop down.
In the box next to it, enter the formula


='Sheet 1'!M2


(Change the sheet name if necessary)

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

Excel will automatically adjust the formula to ='Sheet 3'!M3 for B3, etc.