Why Excel Conditional Formatting does not work the same as formula? I have a couple thousand rows in a file. Column A has `AAA`

value, column B has `BBB`

value.

```
Column A Column B
row1 1000 10
row2 2000 20
row3 3000 30
```

If I want to write a formula `A1*B1`

it will give a result 10000. When I drag it, it will automatically change source cells so it would give me `A2*B2 = 40000`

.

So why conditional formatting doesn't do exactly the same when you copy formatting to different cell/row?

What I want to do is to set conditional formatting for each row separately:

`x1.row1 > x2.row1`

– make it green.

for second row: `IF(x1.row2 > x2.row2)`

– make it green,

for third row it should be: `IF(x1.row3 > x2.row3)`

make it green, and so on,

But Excel for every next row, compares always to first cell i.e. `x1.row1 > x2.row2`

, `x1.row1 > x2.row3`

and so on.

Is there a way to apply formatting for each row separately, without creating multiple conditions?

## Best Answer

From my understanding, you want to do a conditional formating of every cell in column x1 if it's value is higher than it's corresponding cell in column x2. To do it :

First, setup your conditional formating on the first cellConditional formating -> New ruleConditional Formatting Rules Manager, clickNew Rule...Use a formula to determine which cells to formatFormat values where this formula is true :dollar signs`$`

you want so that the formula would usually update when the cell is copied if you were copying cells in Excel.`=$A1>$B1`

or`=A1>B1`

. It is important that there is not`$`

in front of the`1`

.Formatbutton.Okbutton) and go back to theConditional formatting rules managerwindow. You will see your new rule.Apply your rule to the area you wantThere are 2 ways to do it :

In the

Conditional formatting rules managerwindow, under theApplies tocolumn, change the range you want your conditional formatting to be applied to the whole range you want.Directly in the worksheet (Closing the

Conditional formatting rules manager), copy the format of your first cell to all the rows you want. There are many ways to do so...Format PainterCopying you first cell by dragging it and then clicking the contextual menu

->Fill formatting OnlySee here for more information about it.

And you are all set!