Add-on to this code for additional scenerio.

[klarowe]

Right now I have these reports set up so that with certain ranges, an offset cell is trimmed down to produce a tolerance range. From that, the target cell background is filled in with either red or green depending on if the actual dimension is within tolerance. It works perfectly with any offset cell entry of ' X.XXX" - Y.YYY" ' however there are a couple ranges where instead of having a this - that range, it will be just a reference dimension and will read ' X.XXX" REF '. If this is the case, then the toleranced dimension is within +/- .010" of the reference tolerance (hopefully that makes sense).
Example: X.X10" REF = X.X00" - X.X20".

Now is there a way to add an if statement to this code that will look for that "REF" (may or may not be capitalized) and if it exists, then it will trim the ' " ' and the "REF" and set the toleranced range to be +/- .010" of the remaining number???

I really hope this makes sense to someone b/c I'm sure its getting confusing.. lol.

here is the code I have now thanks to one of you guys (can't remember who, sorry).

Sub ColoredTolerances()
    Dim rng As Range
    Dim nm As Name
    Dim i As Variant
    Dim Dn      As Range
    Dim temp    As Double
    Dim temp1   As Double
    Dim temp2   As Double
    Dim Dmin
    Dim Dmax
 
    For Each nm In ActiveWorkbook.Names
        If nm.Name = "PinionJnl1" Or nm.Name = "PinionJnl2" Or nm.Name = "PinionJnl3" Or nm.Name = "PinionJnl4" Or nm.Name = "PinionJnl5" _
            Or nm.Name = "PinionJnl6" Or nm.Name = "PilotP1" Or nm.Name = "PilotP2" Or nm.Name = "PilotP3" Or nm.Name = "PilotP4" _
            Or nm.Name = "PilotP5" Or nm.Name = "PilotP6" Or nm.Name = "PilotFit1" Or nm.Name = "PilotFit2" Or nm.Name = "PilotFit3" _
            Or nm.Name = "PilotFit4" Or nm.Name = "PilotFit5" Or nm.Name = "PilotFit6" Or nm.Name = "LabyD1A" Or nm.Name = "LabyD2A" _
            Or nm.Name = "LabyD3A" Or nm.Name = "LabyD4A" Or nm.Name = "LabyD5A" Or nm.Name = "LabyD6A" Or nm.Name = "LabyD1B" _
            Or nm.Name = "LabyD2B" Or nm.Name = "LabyD3B" Or nm.Name = "LabyD4B" Or nm.Name = "LabyD5B" Or nm.Name = "LabyD6B" _
            Or nm.Name = "LabyD1C" Or nm.Name = "LabyD2C" Or nm.Name = "LabyD3C" Or nm.Name = "LabyD4C" Or nm.Name = "LabyD5C" _
            Or nm.Name = "LabyD6C" Or nm.Name = "TBLength1" Or nm.Name = "TBLength2" Or nm.Name = "TBLength3" Or nm.Name = "TBLength4" _
            Or nm.Name = "TBLength5" Or nm.Name = "TBLength6" Or nm.Name = "TBDiam1" Or nm.Name = "TBDiam2" Or nm.Name = "TBDiam3" _
            Or nm.Name = "TBDiam4" Or nm.Name = "TBDiam5" Or nm.Name = "TBDiam6" Or nm.Name = "TBFit1" Or nm.Name = "TBFit2" _
            Or nm.Name = "TBFit3" Or nm.Name = "TBFit4" Or nm.Name = "TBFit5" Or nm.Name = "TBFit6" Or nm.Name = "BrgBore1" _
            Or nm.Name = "BrgBore2" Or nm.Name = "BrgBore3" Or nm.Name = "BrgBore4" Or nm.Name = "BrgBore5" Or nm.Name = "BrgBore6" _
            Or nm.Name = "GearJnl1" Or nm.Name = "GearJnl2" Or nm.Name = "LabyT1A" Or nm.Name = "LabyT1B" Or nm.Name = "LabyT1C" _
            Or nm.Name = "LabyT2A" Or nm.Name = "LabyT2B" Or nm.Name = "LabyT2C" Or nm.Name = "LabyT3A" Or nm.Name = "LabyT3B" _
            Or nm.Name = "LabyT3C" Or nm.Name = "PolyDiam" Or nm.Name = "PolyFit" Or nm.Name = "BackPlateDiam" Or nm.Name = "GboxPlateDiam" _
            Then
 
            Set rng = Range(nm.Name)
            On Error Resume Next
            For Each Dn In rng
                If rng.Value <> "" Then
                    Dmin = Val(Trim(Replace(Split(Dn.Offset(, 2), "-")(0), """", "")))
                    Dmax = Val(Trim(Replace(Split(Dn.Offset(, 2), "-")(1), """", "")))
                    If InStr(Dn, "-") Then
                        temp1 = Val(Trim(Replace(Split(Dn, "-")(0), """", "")))
                        temp2 = Val(Trim(Replace(Split(Dn, "-")(0), """", "")))
                        Dn.Interior.ColorIndex = IIf(temp1 >= Dmin And temp1 <= Dmax And temp2 >= Dmin And temp2 <= Dmax, 4, 3)
                    Else
                        temp = Replace(Dn, """", "")
                        Dn.Interior.ColorIndex = IIf(temp >= Dmin And temp <= Dmax, 4, 3)
                    End If
                Else: Dn.Interior.ColorIndex = 0
                End If
                If rng.Offset(, 2).Value = "" Then Dn.Interior.ColorIndex = 0
                If rng.Value = "NA" Or rng.Value = "N/A" Or rng.Value = "na" Or rng.Value = "n/a" Or rng.Value = "Na" Or rng.Value = "N/a" _
                        Or rng.Value = "nA" Or rng.Value = "n/A" Then Dn.Interior.ColorIndex = 0
            Next Dn
        End If
    Next nm
End Sub

Thanks in advance!!!

 

[klarowe]

Not sure if it will help any, but here is a screenshot example of one of the sheets:

 

[klarowe]

feeling lonely on pg. 4.. lol

 

[klarowe]

bump... anyone???

 

[klarowe]

Still no luck on figuring this thing out.

 

[klarowe]

Giving this one last shot before I give up on it.... Anyone have any ideas?