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?
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?