Format if not found in addition to found

I am using this code to search several worksheets against a master inventory list. Works pretty good, but there are 2 issues with it.

First, I would like it to format yellow for a good match and red for no match. Currently the yellow for match works nicely.

Also, I am just copy and pasting the find and do part of the code for each worksheet. Is there a simpler way to do this?
I will be searching some 16 worksheets with it.

Please Login or Register  to view this content.

Reasonably confident? Not really, but I learn better when I can take these answers and try to figure them out without a blind copy and pastes.

That being said, changing the interior color ahead of time, then only changing the ones found worked well.
I have two concerns with that method though
- There is a large amount of non valuable text, headings, misc numbers, etc that I would rather have left alone.
- I would eventually like to make a list of the numbers not found. My thought here was to write a separate sub to find all cells with the interior color matching the cells not found.

So an argument to highlight the cells not found would serve more purposes in the long run.

The "For Each ws" code worked really good on a simpler sub in which I cleared the contents of all but 3 sheets.
But I am having trouble understanding how to insert it into the code above. Here is a shorter version with just the ws2 sheet being looked in.

Please Login or Register  to view this content.

I'll keep tinkering with it. I am just googling different codes and trying to understand the syntax. I have a long way to go.

Awesome, every line helps.

The INVMaster List is compiled by our server wide inventory system. It is supposed to be what we are accountable to have stocked.
This list is copy and pasted onto the worksheet.

The BI2C sheets and the subsequent 18 or so other sheets contain what we have physically inventoried.

(Somewhat unrelated, these sheets are populated by a "All Warehouses" sheet that contains a 2D map of the warehouses and each product in its given location. I have a subscript that scans that sheet and then lists the products by category on these physical inventory sheets.)

So the immediate goal here is to have excel highlight cells that contain values that are on the INVMaster sheet AND ALSO the other physical inventory sheets one color, say Yellow, and then the values that are found ONLY on the INVMaster sheets and not the physical inv sheets a separate color, say Red.


And eventually I would like to have a separate sub that would generate a list of the values that are in Red. This is why I was looking for a different code to label those values.



If it would be significantly helpful I could put together a mock sheet that resembles what I am working with and post it. I just have to change some of the entry data for company privacy etc.

Here is a cleaned up version of what I am working with.

Some names and details have been changed, but the only significant change is that there are only two physical inv sheets instead of 18.

The location is simply for our reference and doesn't involve any formulas etc.

We are checking the INVMaster list against what we say is physically here.


Maybe this will clarify my questions a bit.

Thanks!

Sorry for the delay, this isn't by any means my full time job.

I am a Supervisor and this is a solution to a problem, but one of many I'm afraid.



Thanks for the different perspective, it gives me another way to look at these types of things.



One question, on the sheet generated you have the line

Please Login or Register  to view this content.

Why would it be labled not found when it was found?


This sheet would ideally be used by a handful of people in our facility, some of which may not be excel masters. My goal is to make a sheet that is extremely user friendly. Generating a new sheet on top of what is already present may just be enough to scare off someone who is new to excel.


But overall every example of code helps me problem solve my way through and I genuinely say thank you for your time and effort.

I will store this aside for the time being, I am met with the challenge of having something perfect, or having something at all.
A more important function I am going to be working on next is reversing the check color function and having it search the individual product sheets as well.


I will use your same principle of making them all red, then highlight yellow if found, but honestly its going to be a bit of a challenge for me to get the code reversed.


Highlighting if not found is a nice feature I will come back to later, but I need to prioritize my functions for the time being.

What inconsistencies?

Seemed to work ok with my codes. Just not with the functionality I was hoping for.

Ah yes.....that is actually exactly it will be in real life.

This is a copy and pasted report from our computer inventory program.

Not much I can do to make it all line up without making this process no longer a time saver

I have never used a trim before.


So when you put in Case 51 and Case 52 this was to compensate for the different lengths of the useless data in the cell?

So then we would need more mid statements to compensate for the variety? We would thus create more Cases?


Are we creating a fixed case for each possible scenario?

Cell A1 for example may not always be trimmed at the same amount.
I would rather not rewrite each case everytime.

Thanks for your patience