Using Case Statement for Conditional Formatting

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.





#

Sub CondFormatAssyP1()
    
    
    
     'Rivet Height
        Dim a As Double
        Dim b As Double

     'Rivet Diameter
        Dim c As Double
        Dim d As Double
        
    ActiveWorkbook.Activate
    ActiveWorkSheet.Sheets("Assy Part 1").Activate
    
  Select Case Range("D2")
    Case TW10, TW66, TW67, TW72
          a = 1
          b = 0.6
          c = 5
          d = 6.25
           
            Select Case Range("F9:M9")
        
                Case Is > a
                Cell.Text.Color = vbGreen
                Case Is < a
                Cell.Text.Color = vbRed
                Case Is = Null
                Cell.Text.Color = vbBlack
                
            End Select
       
        Select Case Range("F10,H10,J10,L10")
            
                Case c To d
                Cell.Text.Color = vbGreen
                Case Is < c
                Cell.Text.Color = vbRed
                Case Is = Null
                Cell.Text.Color = vbBlack
                
            
          
     End Select
  End Select
     
'Next Set of Parts

#


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