trying to format if AHT is found in a range of values

[Matthew Dyer]

I'm having some issues and hoping you guys might be able to help me
out. I have a variable (AHT), and i'm trying to format a cell in my
spreadsheet based on the value of AHT. I'm about to pull out my hair
trying to figure it out.

If (AHT < 4.03) Then Range("e2").Interior.ColorIndex = 3 -- simple
enough.

Where I get into trouble is here...

If (AHT >= 4.03) And (AHT <= 4.33) then Then
Range("e2").Interior.ColorIndex = 45

logically, this makes sense, but I messing something up when stacking
several of these compound if statements together. I've got 7 different
'ranges' that AHT may fall in.

1. if AHT is less than 4.03, e2 fill color should be 3.
2. If AHT is from 4.03 to 4.33 then "e2" fill should be 45
3. if AHT is from 4.34 to 4.66 then "e2" fill should be 6
4. if AHT is from 4.67 to 5.09 then "e2" fill should be 8
5. if AHT is from 5.10 to 5.52 then "e2" fill should be 6
6. if AHT is from 5.53 to 5.95 then "e2" fill should be 45
7. if AHT is greater than 5.95 then "e2" fill should be 3

Since this has to be done via macro, conditional formatting is
unfortunately not an option. Could someone please help me out? Thanks
in advance guys!

 

[michael.beckinsale]

Hi Mathew,

May l suggest you use a select case statement something like:

AHT = Range("A1")
Select Case AHT
Case AHT < 4.03
Range("E2").Interior.ColorIndex = 3
Case AHT = 4.03 To 4.33
Range("E2").Interior.ColorIndex = 45
etc...........
End Select

Regards

Michael

 

[Dave Peterson]

I wouldn't bother with the lower bound in the Case statements:

AHT = Range("A1").Value
Select Case AHT
Case AHT < 4.03
Range("E2").Interior.ColorIndex = 3
Case AHT < 4.33
Range("E2").Interior.ColorIndex = 45
etc...........
End Select

The first condition satisfied will be used. The remaining won't be checked.