Is there a way of listing the row number of cells that have triggered a conditionally formatting rule in another sheet?

user_18835318 0
Viewed 44

I'm looking for a way to list all the row numbers of cells that have triggered a conditionally formatting rule (that changes the cell colour red or yellow) into another worksheet. Is this possible? I have a 180,000 rows and I'm having to zoom out to 10% then scrolling down to see the highlighted cells. It's properly time consuming

Not looking for the code, just looking for a nudge in the right direction. Thanks

conditional-formatting excel
1 Answers

180,000 rows with conditional formatting, wow, that will take a while to refresh.

While you cannot create a formula based on the output of conditional formatting, you CAN create a formula that incorporates the same processing logic as conditional formatting. I would approach it like this:

  • add a column to the sheet with the original data
  • create a formula that uses the same input as the conditional formatting, but returns a text or a number instead of applying a color
  • on a separate sheet, use the new Dynamic Array Filter() function to filter the data by the output of the new formula column.

Filter() is documented here: https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759

If you run an older version of Excel that doesn't have Filter, you can sort the data table by the new column to help locate the rows that meet/don't meet the condition in the formula column.

user_4479700
Related