Search code examples
excelconditional-formatting

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

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

Solution

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