Conditional Formatting

Hi Naqiym

The "...variable AA remains static..." because the default cell/range selection in conditional formatting gives absolute references i.e. there's a $ sign to fix the column and a second dollar sign to fix the row e.g. $AA$2. In your case you want only the column to be fixed but the row to change between rows (like if you copied formula to other rows). That requires $AA2 (e.g.).

To do that in your sheet,

1. Select a cell in row 2 and in the ribbon go Home/Conditional Formatting/Rules Manager...
2. Pick first rule (pink) in the list under Rule (applied in order shown) then click Edit Rule. It says Cell Value... greater than  =$AA$2 
3. Change the last box to read:

   $AA2

    then click Ok.
4. Back in the Rules Manager, for that rule, change "Applies to" from =$B$2:$Y$2 to

   $B$2:$Y$440

    - this range can be fixed. Click Apply (You will see pink formatting appear in many cells)
5. Do the same for the second (green) rule but I suggest you change it from "less than" to "less than or equal to" (so you don't have white cells if any value matches that in AA). In the file attached, I've changed it to that test.
6. Carefully delete the other two rules and click Ok. (You will see green formatting also appears in many cells).

That's it!

Hope this helps (please check the attached revised file to see the results).