Coloring one in between cells with similar names

Hi Arash and welcome to the Forum.

In the attached file, I've done that with conditional formatting and a "helper column" F (which can be hidden). The good thing about this is that it works even if your rows aren't sorted.

In that column, I put just the value 2 in cell F1 then copied this formula in cell F2 down:

=IF(A1<>A2, -F1,F1)

You'll see that the values are either negative or positive for groups of values in column A.

I then made two "Use a formula.." conditional formatting rules for cell A1. The first to give green format fill is:

=IF($F2<0,1,0)

Note the less than sign and that there  must be a $ with column name only.

The second to give orange fill is: 

=IF($F2>0,1,0)

I then went back to Manage Rules and changed the range of "Applies to" from =$A$2 to:

=$A$2:$D$14

(Take care and check the text when you do that, Excel can add cell names if you move the cursor so it's better to paste the words above). I made it A...D so that all columns of the table are coloured similarly.

Now if you change a value, the colour bands will react. You can change the conditional formatting colurs (via Manage Rules) but remember that if the rules don't apply, any underlying cell colour will be revealed.

You can extend that $D$14 limit but don't forget to copy the helper column down. In fact you could move it to another (hidden) sheet and have the formula copied to 200 rows  say.

Hope this helps.