I have a sheet with statistical data to which I had applied color scale conditional formatting. Some rows I set to have green for highest and red for lowest number in row and other rows the other way around because I lower score would be better than lower.

But when applying a filter to the sheet and changing the order, I noticed that the conditional formatting got messed up and rows that I had set to have the highest number green had become red and visa versa.

Is it possible to solve this with a conditional formatting that is only applied when cell in another column has a certain value?

for example:

If the cell in column C in the same row has value "metric A" then apply color scale green to lowest en red to highest number.


if the cell in column C in the same row has value "metric B" then apply color scale red to lowest and green to highest number.

or is there a better way, or some basic google sheet conditional formatting rule that applies when using filters that I am not aware of?


2 Answers 2


Unfortunately, color scale conditional formatting can't be used based on another cell condition. This can be done only in single color conditional formatting.


You can apply Conditional formatting based on a Formula that evaluates to TRUE or FALSE. This is quite versatile, but it does not allow for using such formula as a condition for applying the color scale based on that.

One option is to define another conditional rule that reformats the target cells, based on the opposite criterion (negated formula).

Not the answer you're looking for? Browse other questions tagged or ask your own question.