My conditional formatting and data validation keeps breaking when it wasn't previously.

I have a daily report I have to review and I built a workbook to make the process more digestible.

Here is the basic structure of my workbook/worflow:

Each day I extract a report to a CSV file. I save it in a designated folder and replace the previous days report.

Sheet 1 has a power query mapped to where I save to report I refresh the data which updates the table. I apply some filters/delete items/sort rows (I deleted via deleting table rows). I have a macro button that sends all of my filtered and sorted data to my Daily_Maint table on sheet 2.

Sheet 2 Daily_Maint table I have additional columns for manual data. One column on the end for comments (which has no issues). Second to last column is a review status column which has data validation to a list on a separate sheet (Completed, outstanding, system maintenance, circle back). I have (or had) conditional formatting for the list options to color code the list options (green,red, yellow, orange respectively). In another column (one which lists different users) I had conditional formatting to change the color for 2 specific users. In a column that lists client number, I have the cells formatted to insert letters before the numbers. Applied to the whole table I have conduit for atting which it looks at the column with client numbers (aka RM #'s) and when the number is diff from the cell above it, I have formatted a border across the whole table. (This visually separates groups of maintenance by customer).

Once I have completed my review, I make a copy of the sheet and rename it to the date of the report. I delete the rows in the Daily_Maint table and do it all again the next day.

Recently our drives were remapped and I had to remap my power query. When that happened my macro broke... Which didn't make sense because the macro and power query shouldn't talk to each other. As if by magic the macro started working again.

What I'm struggling with:

1. Data validation keeps breaking. This was rarely an issue before the remapping. But now it breaks every day and I can't figure out why.

2. Conditional formatting for color coding keeps breaking. Origioally O tried applying it to =$O:$O which worked well until it didn't. I tried applying it to an absurdly large range like =$O$6:$O$20000 but after I delete rows it changes the applied to formula to what ever number was after the range I deleted. (Ex $O$500:$O$19500)

3. Similar situation with the condition l formatting for the usernames. Tried =$M:$M and this worked really well for months... Until it stopped.

4. Conditional formatting for the line to visually separate groups by customer. My original formula was =$F7<>$F6 applied to the full sheet. This would break every day but it was an easy enough fix I was fine living with. But after talking to AI I tried =INDEX($F:$F,ROW())<>INDEX($F:$F,ROW()-1). I thought it was a permanent fix because I tested it several times. But when I actually use it... It duplicates/breaks/still needs fixing every day.

I've tried applying the formatting to "This Table", "This worksheet", and "Current Selection" and nothing is working.

What am I doing wrong? Would it be more stable if I built everything on the worksheet and not in a table? I think I'm too close to it at this point to have any perspective and I don't have that much excel experience, most of my knowledge I've gained through building this spreadsheet. I appreciate any advice.