Need help checking cells for correct values

[pasqualebaldi]

I have attached a sample file to help show what I am about to explain. I am trying to make a tool that will check a weekly generated report for errors.

Check Values Sample File.xlsx

I need a code that will check column A for the value "Employee". If "Employee" is found, then check column B for a job name. If there is no value, then continue to the next "Employee" value in column A. If there is a value, then check column C for the Labor Type. Take the Labor Type value and look on 'Sheet2' to find the matching Job and Labor Type value and make sure the Rate on 'Sheet1' in column D is the same as 'Sheet2' column C. If the value is not the same, then I would like to turn the rate in 'Sheet1' red to show the error.

In my sample file, I only have 3 employees, 2 jobs, and 2 labor types. These three lists will be a different length each week. So I am hoping this can be a dynamic code.

Is this possible to achieve?

Thank you very much!

[sourabhg98]

Why troubling yourself in typing all these manually.
Formulas can do that for you.

Main formula cell--H3
Each time you want to input labour rate, just copy the main formula cell and paste in the desired range.

Please Login or Register  to view this content.

[pasqualebaldi]

Why troubling yourself in typing all these manually.
Formulas can do that for you.

Main formula cell--H3
Each time you want to input labour rate, just copy the main formula cell and paste in the desired range.

Please Login or Register  to view this content.

The reason for this is because once a week I will generate a report from a program that will export into a new excel file. So I was going to try to create a tool in my toolbar that uses code that will refer to a separate file with all the Job and Labor type data. That way I can Change the data as needed and still be able to press a button in my toolbar for each report.

In my sample I am using two sheets, but it will really be two files. I was just trying to simplify it to get help and then later modify it to my needs.

Does this make sense?

[stnkynts]

There may be a more efficient way to do it but this was off the top of my head:

Please Login or Register  to view this content.

[sourabhg98]

Although, you can easily compare the results with a macro provided by @stnkynts , but I still believe that that would be easier for you to use a formula to extract labour amounts. So, why don't you keep the main formula cell in H3 of the sheet2 and create a macro which will copy the whole sheet2 to the new file which you will get each week. Then you can use the formulas easily.

However, it is just a suggestion. You can surely go for the macro provided above @stnkynts if you particularly want to do that.

[pasqualebaldi]

There may be a more efficient way to do it but this was off the top of my head:

Please Login or Register  to view this content.

I believe this is going to work great for me. The only thing I can seem to adjust is how it searches. On Sheet1 for job, my report generates additional info with the job. For example, 1310-/ 2020-. My job number is 1310, and the code seems to work if I delete everything in the cell except for 1310. Is there a way to match the job numbers from Sheet1 and Sheet2 without having to have the job values match exactly?

[stnkynts]

Is there a way to match the job numbers from Sheet1 and Sheet2 without having to have the job values match exactly?

I depends, will it always be a 4 digit number?

[pasqualebaldi]

I depends, will it always be a 4 digit number?

Yes, the job number will always be a 4 digit number.

[stnkynts]

Maybe this. It would have been best if you would have submitted an accurate representation of your workbook.

Please Login or Register  to view this content.