Im having a lot of trouble figuring this out and after a ton of search-fu I need to ask an expert. I need a formula that will change the background color of a cell in column G if its value is greater than the value of the cell next to it in column F. Ive tried all kinds of formulas and Im not sure if I need to select all cells in column G then go to Conditional Formatting>Highlight Cell Rules>Greater Than to apply a forumla or just use New Rule on a single cell and drag it out to the rest of the column. I seem to be finding conflicting answers.
Excel – Change Cell Color if One cell Is Greater Than Another
microsoft excel
Related Solutions
Edit: I think you would not be able to use conditional formatting this way with icon sets. I got the following error when I tried:
You cannot use relative references in conditional formatting criteria for color scales, data bars, and icon sets.
I was, however, able to achieve the same by applying this formula to right column and then applying conditional formatting rule on this column as show in the screen shot.
=IF(OFFSET(E10,0,-1)>500,1,IF(OFFSET(E10,0,-1)=500,0,-1))
The formula should be:
=OFFSET(E10,0,-1)>500
In Excel, the Offset function returns a reference to a range that is offset a number of rows and columns from another range or cell.
The syntax for the Offset function is:
Offset( range, rows, columns, height, width )
- range is the starting range from which the offset will be applied.
- rows is the number of rows to apply as the offset to the range. This can be a positive or negative number.
- columns is the number of columns to apply as the offset to the range. This can be a positive or negative number.
- height is the number of rows that you want the returned range to be.
- width is the number of columns that you want the returned range to be.
Conditional formatting in Excel 2007 (based on value of another cell)
Select the cell you want to format, “A1″ in this example.
In the Home tab, Styles group, click on Conditional Formatting.
Select New Rule.
The New Formatting Rule window will open.
Click Use a formula to determine which cells to format.
The New Formatting Rule window will now look like this:
In the edit box labeled “Format values where this formula is true“, enter “=” followed by a formula referencing the other cell. In this example the formula: “=B1<0″ is entered.
Click the Format button. The Format Cells window will open.
Specify the formatting you want. In this example I changed the cell color to “RED”.
Click OK to close The Format Cells window.
Click OK to close The New Formatting Rule window.
To summarize this example: cell A1 will have a RED color when cell B1 value is less than zero.
Best Answer
You would use the formula:
Then you would apply it to All of Column G: