Using color scale conditional formatting on filtered data

[moty.98]

Hi,

I have a table with filtered data. I'm trying to use the color scale conditional formatting only on the data which is filtered and displayed, but what in fact happens is that the color scale is coloring also the hidden and not filtered cells.
An example for that is in the attached sheet. I color scaled column B filtered figures, but there are no green cells, because they are hidden. I'd like only the four figures to be colored.


How can I color only the filtered cells?


Thank you,

Moty



P.S. I know I can copy paste the filtered data, but I prefer not to do that, and I know I can select only these 4 figures in the example, but that's just an example for data table which has thousands of cells and columns

[Andy Pope]

Add a column with this formula and apply CF to new column.

=IF(B2<>SUBTOTAL(109,B2),NA(),B2)

Does that give you what you want?

[moty.98]

Thank you Andy for your answer. It partly help me.

I'd like to be able to do that conditional formatting on the filtered data itself if possible. Since I have large scale of columns, I need to constantly filter and color the data.

[Andy Pope]

I don't understand.

See attached includes copy of cells with CF applied to all items and then manually adjust >200 items.
This then matches the CF output on the new formula base column