Skip to main content
replaced http://webapps.stackexchange.com/ with https://webapps.stackexchange.com/
Source Link

One can have alternating coloring for filtered views that involve only hiding some rows. This can be done by using the command subtotal which ignores the cells hidden due to a filter.

You should have a column, for example A, which is non-blank in every row that contains data. Then apply conditional formatting to A1:Z (for example) with custom formula

=iseven(subtotal(3, $A$1:$A1))

The code 3 means "count nonempty cells in the range". I.e., like counta but with filtered-out cells hidden.


If the cells are also sorted in the filtered view, there is nothing to be done: since the sort happens after any formatting is done, there is no function that can see into the future and predict how the rows will be sorted. The only suggestion I have is the same as in my earlier answermy earlier answer: replace filtered view with sort and filter on another sheet. Then, the conditional formatting can be applied with the simple iseven(row()) and will remain alternating.

One can have alternating coloring for filtered views that involve only hiding some rows. This can be done by using the command subtotal which ignores the cells hidden due to a filter.

You should have a column, for example A, which is non-blank in every row that contains data. Then apply conditional formatting to A1:Z (for example) with custom formula

=iseven(subtotal(3, $A$1:$A1))

The code 3 means "count nonempty cells in the range". I.e., like counta but with filtered-out cells hidden.


If the cells are also sorted in the filtered view, there is nothing to be done: since the sort happens after any formatting is done, there is no function that can see into the future and predict how the rows will be sorted. The only suggestion I have is the same as in my earlier answer: replace filtered view with sort and filter on another sheet. Then, the conditional formatting can be applied with the simple iseven(row()) and will remain alternating.

One can have alternating coloring for filtered views that involve only hiding some rows. This can be done by using the command subtotal which ignores the cells hidden due to a filter.

You should have a column, for example A, which is non-blank in every row that contains data. Then apply conditional formatting to A1:Z (for example) with custom formula

=iseven(subtotal(3, $A$1:$A1))

The code 3 means "count nonempty cells in the range". I.e., like counta but with filtered-out cells hidden.


If the cells are also sorted in the filtered view, there is nothing to be done: since the sort happens after any formatting is done, there is no function that can see into the future and predict how the rows will be sorted. The only suggestion I have is the same as in my earlier answer: replace filtered view with sort and filter on another sheet. Then, the conditional formatting can be applied with the simple iseven(row()) and will remain alternating.

added 626 characters in body
Source Link
user79865
user79865

TheOne can have alternating coloring for filtered views that involve only hiding some rows. This can be done by using the command subtotal has the nice property of ignoringwhich ignores the cells hidden due to a filter.

You should have a column, for example A, which is non-blank in every row that contains data. Then apply conditional formatting to A1:Z (for example) with custom formula

=iseven(subtotal(3, $A$1:$A1))

The code 3 means "count nonempty cells in the range". I.e., like counta but with filtered-out cells hidden.


If the cells are also sorted in the filtered view, there is nothing to be done: since the sort happens after any formatting is done, there is no function that can see into the future and predict how the rows will be sorted. The only suggestion I have is the same as in my earlier answer: replace filtered view with sort and filter on another sheet. Then, the conditional formatting can be applied with the simple iseven(row()) and will remain alternating.

The command subtotal has the nice property of ignoring the cells hidden due to a filter.

You should have a column, for example A, which is non-blank in every row that contains data. Then apply conditional formatting to A1:Z (for example) with custom formula

=iseven(subtotal(3, $A$1:$A1))

The code 3 means "count nonempty cells in the range". I.e., like counta but with filtered-out cells hidden.

One can have alternating coloring for filtered views that involve only hiding some rows. This can be done by using the command subtotal which ignores the cells hidden due to a filter.

You should have a column, for example A, which is non-blank in every row that contains data. Then apply conditional formatting to A1:Z (for example) with custom formula

=iseven(subtotal(3, $A$1:$A1))

The code 3 means "count nonempty cells in the range". I.e., like counta but with filtered-out cells hidden.


If the cells are also sorted in the filtered view, there is nothing to be done: since the sort happens after any formatting is done, there is no function that can see into the future and predict how the rows will be sorted. The only suggestion I have is the same as in my earlier answer: replace filtered view with sort and filter on another sheet. Then, the conditional formatting can be applied with the simple iseven(row()) and will remain alternating.

Source Link
user79865
user79865

The command subtotal has the nice property of ignoring the cells hidden due to a filter.

You should have a column, for example A, which is non-blank in every row that contains data. Then apply conditional formatting to A1:Z (for example) with custom formula

=iseven(subtotal(3, $A$1:$A1))

The code 3 means "count nonempty cells in the range". I.e., like counta but with filtered-out cells hidden.