This user is solving a real formatting problem with the wrong tools. Conditional formatting, Power Query, and VBA are all possible paths forward, but only one of them respects the user's time and sanity. The answer is Power Query, and it is not a close call.
The core challenge here is structural: data that lives in "index cards" with variable row counts, fixed headers and footers, and a need to flag empty cards by color. Conditional formatting can handle the color fill based on column B values, but it cannot dynamically define the "cutoff line" or cleanly isolate card boundaries. The formula would require nested logic that checks for repeating patterns, and even then, it would only apply formatting, not remove the empty cards. The user would still be manually sorting and deleting. That is not automation; it is semi-automated busywork.
VBA is a reasonable second choice for someone who already knows it, but the user's phrasing suggests they are not deeply familiar with macros. Writing a VBA script to loop through rows, detect card boundaries, and apply fills is doable but fragile. A single change in the Google Sheet structure or paste format breaks the macro. The user would inherit maintenance debt along with the solution.
Power Query is the right fit because it treats the problem as data transformation, not formatting. Import the pasted values, use the fixed top and bottom lines as delimiters to split the cards into distinct rows, add a column that identifies empty cards based on column B values, and output a clean table. The color fill becomes a simple conditional format on that cleaned table. The user gets one repeatable process that handles any number of rows per card, no manual sorting, and no fragile code. The learning curve is real but shallow, Excel 365's Power Query interface is guided and visual.
What this user really needs is not a formula or a macro. They need a workflow that matches how their data actually behaves: variable, repetitive, and messy. Power Query gives them that. Our recommendation is straightforward: invest the hour to learn Power Query's "Group By" and "Custom Column" steps. It will save dozens of hours of manual formatting over the life of this sheet. Stop trying to force conditional formatting to be a data cleaner. Use the right tool for the job.