How to apply conditional formatting only if cell has X value? [google sheet] [duplicate]

Question

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.

and

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?

Answer 1

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

Answer 2

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).