Excel Conditional Formatting column F < column G

0
votes

I am trying to develop a conditional formatting rule that will handle all the data in the columns. The annoying thing with Excel is that it only wants an absolute reference to one cell. I do not want to set rules for every cell individually.

So what I need to do is compare two cells completely down the sheet. For example:

if Cell F3 < Cell G2 = true //Then do something with formatting in Cell F3
if Cell F4 < Cell G3 = true //Then do something with formatting in Cell F4
Also
if Cell F3 > Cell G2 = true //Do something different with formatting in Cell F3

This will continue down the entire sheet. All I am doing is trying to automatically change the text color of the cell in column F.

Is there away to create one rule that will control the whole sheet?

3
excelconditional-formatting
I am not sure how to add images to overflow from local host. So F3 and G2 are just cell references. Also it does not matter what the final format is for this question. All I want is a rule that compares the data in cell F3 to the data in cell G2 and keeps that format down the entire sheet. So cell F4 compared to G3, F5 to G4 and so forth. I just do not want to have to create that rule individually for every single cell. – IModulo5
So, supposing F3 < G2, which cells would get highighted Green? – John Bustos
F3 would be highlighted – IModulo5
So you compare the two cells and the cell in Column F will always be the one changed. Column G represents an expected target and Column F represents the actual amount. If the actual is less than the target highlight green if it is greater than the target highlight red – IModulo5
@CustomX Essentially that is it yes. However I am trying to avoid a creating hundreds of individual rules for each cell in the sheet. That is the purpose of this question, I can do it manually but that would take awhile and be tedious. – IModulo5

3 Answers

1
votes

Assuming you want no conditional formatting for when F3=G2 (or one rule could be avoided), please select F3 to as far down as required in ColumnF and add Conditional Formatting formula rules:

For say green: =F3<G2
For say red: =F3>G2

1
votes

Conditional formatting does not require you to format cells individually.

For you example, the easiest way to do this would be:

  1. Highlight cells F3 down
  2. Select the Conditional Formatting > New Rule menu item
  3. Select "Use a formula to determine which cells to format"
  4. In the formula bar, put in =F3<G2
  5. For format, change shading to Green

And do the same (steps 2-5) for Red and =F3>G2

That should do it for the entire highlighted region.

Hope that makes sense

0
votes

EDIT: thanks to pnuts I have edited my answer and provided the correct formula. In your case you need to edit B with F and C with G.

Apologies for the incorrect answer at first :)

enter image description here