An odd-ball One - Cell value range to code

[klarowe]

This one is kind of hard to explain, but I'm going to give it a shot.
Basically on this report I have been working on, a few sheets have some dimensional data. In one column there is an actual dimension and another has the drawing dimension (spec). SOmething like this:
___A________B_________C______________D_______
Actual:___ 1.1234"___ Drawing:___ 1.1000" - 1.2000"

What I want to do is set the "B1" cell interior color to be green if the actual dimension falls within the drawing spec, and red if it does not.
I know I can use a select case to get it to make the color, but what I can't figure out is how to use the entered data in D1 to change from ".1000" - .2000"" to .1000 to .2000 and then transfered into my macro as the case.
Hopfully that makes sense to someone... lol.

A couple notes. In almost all cases, how it is written in the example is exactly how it will be... with the exception of possibly only 3 decimal places instead of 4. But the " will almost always be there, and so will the - . And if it cannot be done without changing the format then I will have to skip on this one b/c it is more important to have the units in the cell than to have the background color.

 

[JamesW]

Hi there,

Try this conditional format:
EDIT: Removing helper cells, bear with me.

=IF(AND(LEFT(B1,FIND("""",B1,1)-1)>=LEFT(D1,FIND("""",D1,1)-1),LEFT(B1,FIND("""",B1,1)-1)<=TRIM(MID(D1,FIND("-",D1,1)+1,FIND("""",D1,FIND("-",D1,1))-1-FIND("-",D1,1)))),1,0)

V. Long; probably a much shorter way, but it works.

 

[MickG]

Try :-

[COLOR="Navy"]Sub[/COLOR] MG11May25
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] temp [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] Dmin
[COLOR="Navy"]Dim[/COLOR] DMax
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    temp = Replace(Dn, """", "")
    Dmin = Val(Trim(Replace(Split(Dn.Offset(, 2), "-")(0), """", "")))
    DMax = Val(Trim(Replace(Split(Dn.Offset(, 2), "-")(1), """", "")))
    Dn.Interior.ColorIndex = IIf(temp >= Dmin And temp <= DMax, 4, 3)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[klarowe]

I'd like to do this with VBA so mick;
How would I incorporate that into my code to enter the value range into my case?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("C10") <> "" Then
        Select Case Range("C10")
            Case 'This is where I need it to say 1.1000 to 1.2000 from the example
                Range("C10").Interior.ColorIndex = 4
            Case Else
                Range("C10").Interior.ColorIndex = 3
        End Select
    Else: End If
End Sub

Also, I would like to have how it appears in the example to stay in the cell, and only have the altered version in my macro.... if that makes sense.

 

[klarowe]

And James: Just for ****s and giggles, I tried various ways of inserting that formula after "Case" but I couldn't get it to work. Was hoping I would maybe get lucky. Is there a way to use it after the Case (basically running the formula as the case)

 

[JamesW]

Hi Klarowe, if you only want to use VBA then use Mick's example.

I thought it might be easier to just use CF and copy it down the range - saving you some trouble.

 

[klarowe]

It would, but wouldn't that automatically change what appears in the cell? Or am I missing something? Also, unfortunately it will appears in a few select rows on just a few sheets. And the rows are not one after another. How do I enter that formula in so that the cell can still be changed

I'm just learning VBA still and I know absolutely nothing about formulas (other than the basics). So I'm not really sure how to use what you posted.. but I'm more than willing to give it a try.... just need some guidance.

 

[klarowe]

I think I've figured out Mick's to get it to work.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Rng As Range, Dn As Range
    Dim temp As Double
    Dim Dmin
    Dim DMax
    Set Rng = Range(Range("C10"), Range("C11"))
    For Each Dn In Rng
        temp = Replace(Dn, """", "")
        Dmin = Val(Trim(Replace(Split(Dn.Offset(, 2), "-")(0), """", "")))
        DMax = Val(Trim(Replace(Split(Dn.Offset(, 2), "-")(1), """", "")))
        Dn.Interior.ColorIndex = IIf(temp >= Dmin And temp <= DMax, 4, 3)
    Next Dn
End Sub

It seems to be working well in my test report.

Edit: I only seem to be having one issue. If I use only 2 ranges (as listed above) it works great. however, if I add any more (max of 4) then it gives me an error. Is there a way to change it so that I can add all 4 into the same code, or do I need to write it twice.
Example of the error:
Set Rng = Range(Range("C5"), Range("C6"), Range("C12"), Range("C13")) <--- Compile Error: Wrong number of arguements or invalid property assignment.

 

[MickG]

Hi, I don't see you need the select case", because The dimension in column "B" will either be within limits "Green" or Outside limits "Red".
I note you refer to column "C" but the Dimension your checking appears to be in column "B", so the code works when you select any cell in column "B".

Private [COLOR=navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] temp [COLOR=navy]As[/COLOR] Double
[COLOR=navy]Dim[/COLOR] Dmin
[COLOR=navy]Dim[/COLOR] DMax
[COLOR=navy]If Target.Count = 1 Then[/COLOR]
[COLOR=navy]If[/COLOR] Target <> "" And Target.Column = 2  [COLOR=navy]Then[/COLOR]
  temp = Replace(Target, """", "")
    [COLOR=navy]If[/COLOR] IsNumeric(temp) [COLOR=navy]Then[/COLOR]
      Dmin = Val(Trim(Replace(Split(Target.Offset(, 2), "-")(0), """", "")))
        DMax = Val(Trim(Replace(Split(Target.Offset(, 2), "-")(1), """", "")))
           Target.Interior.ColorIndex = IIf(temp >= Dmin And temp <= DMax, 4, 3)
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End if [/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[klarowe]

Hi, I don't see you need the select case", because The dimension in column "B" will either be within limits "Green" or Outside limits "Red".
I note you refer to column "C" but the Dimension your checking appears to be in column "B", so the code works when you select any cell in column "B".
Regards Mick

The catch is that it will be in a different column for each sheet. Now it may just be bacause its early, but where is it calling out column b specifically? Then I can change that part of the code (as well as the ranges) for each sheet.
Thanks again