Excel - conditional formatting between two columns of numbers (greater or less than)

Question

I'm trying to come up with a formula, or way of highlighting cells of column G if the value is greater than the cell next to it on the same row in column H.

I can use the conditional formatting style to apply this no problem, but i can't seem to figure out how to select the whole column G against column H in one go. I would have to continuously do the same conditional formatting for every row which pretty much defeats the purpose. Ultimately, I have a large amount of data and want to immediately highlight any items i have greater or equal on hand (column G quantity to my annual usage (column H)

any help with a formula or way of replicating the conditional formatting would be greatly appreciated.

Example: https://www.dropbox.com/s/i88jkqduj5p2soz/conditional%20formatting%20example.JPG?dl=0

Answer 1

For conditional formatting if you select a range of data first then that range will become the "applies to" range and you only need apply the formula that's required for the top left cell of the range - this formula will then implicitly adjust (as if dragged) for the whole "applies to" range, so if you have data in G2:H100 then just select the range G2:G100 and apply this formula in conditional formatting:

=G2>H2

choose format as required> OK

If you genuinely want the whole column then just select column G and adjust my suggested formula to work for row 1

• JFurm: Awesome thank you Barry, much appreciated. Works like a charm!

Answer 2

By default, when you select cells to create Conditional Formating it uses static cell references - $B:$B for example. You need to edit the appropriate references in your formula to remove the static reference flags, the $ symbols.

If you edit the "applies to" references and the formulas to match (only using static references where you really mean to), you can apply Conditional Formatting on entire ranges of cells.

Note: One glitch I discovered long ago (Excel 97, maybe, and Microsoft hasn't seen to fix fixed since) is a bit of "reference drift". Sometimes, after removing the static references and saving the Conditional Formatting rules, it will skew the reference to something really weird like XFD1048576. Go back in and correct them, and the next time it should stick.

• barry houdini: What you describe with the odd references has some logic to it. If you select range A1:B2 but with active cell as B2 then apply a conditional formatting formula like =A1="x" then Excel tries to adjust the formula as if dragged from B2 to A1, because you can't go lower than row 1 or column A the reference changes to last column;last row, e.g.=XFD1048576="x". This can also happen if you change the "applies to" range after you have set the formula
• JFurm: Thanks TJL, the breakdown with the static cell reference "$" was helpful. Turns out the range i selected from Barry's answer "G2:G100" could contain the static cell indicators, however the formula for the conditional formatting had to specifically be =G2>H2 and it works perfectly.