So, I will explain with some exemples in this page, how to use conditional formatting.
In the following worksheet, we have the result of five tests and in column H, the average for each student. You want to color in green the highest average and in red the lowest average.
Now, we add the marks for the sixth course, the average has changed, but the format is still the same and doesn’t match the new result.
In this case, you shouldn’t coloured yourself the background of the cells but the conditional formatting.
In the following document, change the value of any cells to see the impact on the format for the column “Average”.