I have not been able to find how to use the "Select Case" to follow my intended logic. I could use general IF statements, but I'm at the point where I just want to know how to use the select case for this. I want to set a variable based on the value of another variable. Below is my code that is not working correctly. On the second Case If, I want both conditions to be met. I tried using AND and TO between the two tests, but neither is accepted by VBA. As written, VBA behaves as though there is an "OR" between the two test conditions. (X could be any real number).

Select Case X
Case if < 100: Scale = 1
Case if >= 100 < 1000: Scale = 10
Case if >= 1000: Scale = 100
End Select

If X = 1025.26, the second Case If is true because X>=100. If I reverse the order of the Case If's, putting the >=1000 first, the same happens if X = 26.03. The second Case If is again true because X<1000.

Is there a way to check multiple conditions using the Select Case? Thanks.