CODEWITHSUNDEEP
excelexcel-formulaexcel-2007conditional-formatting
demonr6
demonr6

Reputation: 11

Conditional formatting based off different column

I am comparing the data for each column between two tables where the second table will apply conditional formatting if the cell value is higher or lower based off the respective column in table 1.

If the Close Rate in G2 is lower than A2 then red, if higher then green as the example below. I need to do this for every row and adjacent column following the same rules for the entire table. In total I have three tables and right now I am faced with using conditional format for each individual cell and it is a terrible waste of resources. If there is a more simple method someone can share I would really appreciate it. My skills with Excel are okay enough to get me in trouble but not expert level.

http://i.imgur.com/p4sDRwj.png

Upvotes: 1

Views: 315

Answers (1)

pnuts
pnuts

Reputation: 59485

Very, very close! Take out the $s from the two formulae. CF automatically steps through all the cells in the Applies to range but won't adjust the cell references when doing so if these are 'fixed' references.

You also need to apply the CF to the relevant range, so say change =$G$2 to =G2:K17 (the $s here are automatically inserted). Finally, I'd suggest checking Stop If True for both conditions, because they are mutually exclusive.

You may then be able to deal with the other tables with Format Painter.

Upvotes: 1

Related Questions