Custom Sorting with Highlighted Rows

Occasional Visitor

Hello all,


I am working with a very large data set that has Column C, in which every cell says "PAID" or "PENDING." See Image 1 below: I created a valid conditional formatting by creating a cell that says "PENDING" off to the side of my data in Column O and setting =$O$1=$C2 since C1 is the title of the column. I then applied it to the data and the data successfully highlights every row that has a cell in Column C that says "PENDING" red. 


Now, I want to bring every highlighted row to the top of my worksheet. Whenever I do a custom sort, (picture attached) it does bring every row to the top but it fails to highlight the top row. It leaves an empty row all the way at the bottom of my data highlighted red. I can only assume that this highlight is misplaced. Not sure where I went wrong, but it could have something to do with headers.


How do I fix this issue?


Screen Shot 2022-09-13 at 1.00.51 AM.pngScreen Shot 2022-09-13 at 1.00.28 AM.pngScreen Shot 2022-09-13 at 1.00.39 AM.pngScreen Shot 2022-09-13 at 1.02.27 AM.png

1 Reply

@micromaximilian Apply the rule to rows $2:$1048576, since the first data row is row 2.

Demonstrated in the picture below, though on a slightly smaller scale.

Screenshot 2022-09-13 at 07.42.39.png

By the way, do you really need to set the rule to cover over I million rows?