Using Case Statement for Conditional Formatting

[SCHammer843]

If this gets too complicated I am sorry in advance, Also if it cannot be done please advise.
I am very new to VBA and am taking this over from someone that is no longer at company and very good at VBA.
Essentially I have multiple worksheets that are used for data collection in manufacturing production enviroment ie (Cell Kiosks).
Some have a workbook with worsheets for machine (8 max), while some have 3 sheets per machine to allow for changeovers.
On all Sheets I have a drop down selection that decides what part is being checked on all sheets ($D$2).
I want to use Case statements as this will make adding new parts much easier than Data Validation inside Excel. The Formula is so long it make my eyes twitch! As we have as many as 16 part numbers now with more to follow.
I am currently getting a Object missing error. Can someone help and point me in right direction on this. Not sure if this should be done as a Module or Attach to each sheet involved.





#

Please Login or Register  to view this content.

#


On Range Select in second Case statement are where their reading are input.
Much Thanks in advance.

[Vikas_Gautam]

Hi,
Welcome to this forum...
Try this..

Please Login or Register  to view this content.

However I don't know the Purpose of

Please Login or Register  to view this content.

So I didn't include it in the code.. if it matters then you have to explain its purpose to include it in the code..

[protonLeah]

code tags installed.

[SCHammer843]

Sorry was not clear the Case statement with TW10,.... is required to set groups of parts. On this workbook I believe there are 6 groups.
So it would need to look like this i guess.....

Please Login or Register  to view this content.

On the a,b,c,d variables can they be rewritten like i have or have a separate variable for all combinations??

[protonLeah]

Post a sample workbook (with the data validation formula)
To attach a Workbook

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

[SCHammer843]

I have fumbled my way though and got code to work, however it keeps the same text color regardless of value unless re ran though VB editor, only sheet "Assy Part 1" and 1 group of parts have been programmed

Please Login or Register  to view this content.

[SCHammer843]

I finally got every thing to work in harmony

Here's my syntax, Maybe it'll help some one else later on down road
This Sub Selects one of many Part numbers

Please Login or Register  to view this content.

This one is the actual Conditional formatting of said part

Please Login or Register  to view this content.

The Weird Part was i had to add this on individual sheets to make Enter button work?

Please Login or Register  to view this content.

This forum is a very cool resource!