Need help **Urgent**

I already posted regarding a search and got some help with that but I need more help with the same grid. I have a workbook with 2 sheets. Sheet #1 has all the files in which we requested payment from our parent company. Sheet #2 are the files in which the parent company paid us. I used a countif formula in conditional formatting to reference the job number on sheet #1 with the job numbers on sheet #2. If the job # exists on sheet #2 it means we were paid so it will highlight the job # red on sheet #1. Here is what I need now.

#1 -- I think I need a macro. I need to find out a way to delete all the rows that have a cell highlighted in red. In other words, if row 3 has a cell highlighted in red then excel will delete the whole row.

#2 -- Also, I need to find all the jobs we were paid for. Reference the payment column on sheet #1 to the payment column on sheet #2 to see if they match.

Thanks for any help I can get!!

Once you delete the paid rows on sheet 1 you won’t be able to perform the task #2 (Reference the payment column on sheet #1) as the data will be gone.

Seems like having a PAID column on your receivables sheet 1 would make the most sense.
Then you could use AUTO FILTER to show the data you want.
Or a couple of Pivot Tables, one for paid & one for unpaid.

Let's say that I had deleted all the rows that contained jobs that we were not paid for. From there, what would be the formula that I would need to enter in the conditional formatting? I will need this formula to reference the payment on sheet #1 to the payment on sheet #2 to see if they match. Keep in mind that just because one job is on row #2 on one sheet does not mean that it will be on the same row in the other sheet. They will have to be matched by job #. If the payment cell on sheet #1 does not match the coresponding payment cell on sheet #2 then it will be highlighted red.

I am not sure how to do that but I think I can explain the grid a little better.

Sheet #1

Column F contains all the job numbers for which we submitted payment.
Column D contains all the expected payment amounts

Sheet #2

Column E contains all the job numbers for which we were paid
Column T contains the actual payment for each job

Each sheet contains 1300 rows. What I need to do is make sure that we were actually paid for the amount that we submitted for. For example I need a formula that will look at cell F200 in sheet #1. Then go to sheet #2 and find that same job #. Let's say it finds the matching number in cell E300. Then it will go back and look at cell D200 on sheet #1 and read the amount in that cell. It will then reference that against the value in cell T300 on sheet #2 to make sure that it matches. If it does not match then it will highlight cell D200 on sheet #1 red. This way I know that I need to research why we were not paid what was expected. Is this helpful?

see the attached
I added a couple columns to your sheet 1:
H "Payment Status"
I "Variance", so you wouldn't have to look it up manually

Auto filter is turned on so you can simply choose to filter all Unpaid, Over Paid, Short Paid, Paid in Full

You could add some COUNTIF and/or SUMIF to total the various categories, also.


I haven't deleted any paid rows as you requested, though. Seems like a conflict of interest.

That worked great. It actually took care of the first problem I had as well because now that Paymnet clumn is sortable and I can delete the jobs that were paid. Thanks again!