looping through named ranges by naming convention- text versus integer problem

[beckyinDC]

I set up sets of 4 named ranges with "_1" through "_4" for several data items that pertain to quarterly processing.

for example:

CurrentMilestone_1, CurrentMilestone_2, etc
PassedAsOf_1, PassedAsOf_, etc

ok- so I need to color 4 cells with red, yellow or green based upon how many tests passed for that milestone...for troubleshooting, I am just using messageboxes as placeholders:

For i = 1 To 4
Select Case CStr(Application.Range("PassedQuarter_" & i).Text)
Case CStr(Application.Range("YellowThreshold_" & i).Text) To CStr(Application.Range("GreenThreshold_" & i).Text) - 1
MsgBox ("YELLOW! " & i)

Case Is >= CStr(Application.Range("GreenThreshold_" & i))
MsgBox ("GREEN! " & i)

Case Else
MsgBox ("RED! " & i)
End Select
Next


problem-

when the select/case statement evaluated a value of 3, it came up with GREEN when the greenthreshold was 21 and the yellowthreshold was 18- I think it is dealing with them as text instead of integers.

I tried to add cint and then switched to Cstr functions to make this work. Cstr is the right choice since cint returns type mismatch

Also tried "+0" to force the mnamed range values to be treated as numbers- result: type mismatch

Any help? thank you

 

[Norie]

Why are you using CStr and .Text?

Are the values in the named ranges numbers?

Do you actually need any conversion functions?

For i = 1 To 4
        Select Case Range("PassedQuarter_" & i).Value
            Case Range("YellowThreshold_" & i).Value To Range("GreenThreshold_" & i).Value - 1
                MsgBox ("YELLOW! " & i)
            Case Is >= Range("GreenThreshold_" & i).Value
                MsgBox ("GREEN! " & i)
            Case Else
                MsgBox ("RED! " & i)
        End Select
    Next

 

[beckyinDC]

thank you- I was overthinking it and that did work!

I had had some trouble using .value for another issue I was working on and had changed over to referencing the text property when dealing with named ranges...for something it had worked better...but that was something else.

thanks again!

 

[Norie]

Becky

I don't think using Text or Value would actually affect the use of a named range itself.

It might affect the use of the values in the named range though.