Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
trying to format if AHT is found in a range of values
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
trying to format if AHT is found in a range of values
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
trying to format if AHT is found in a range of values
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. On 06/24/2010 04:33, michael.beckinsale wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cond. Format Data Bars of range based on values of another range | Excel Worksheet Functions | |||
Conditional format if cell match found in another range of cells | Excel Worksheet Functions | |||
Identify where max values are found | Excel Discussion (Misc queries) | |||
Check if cell value is found in a seperate range of values | Excel Programming | |||
Search a Range for a phrase and Format cell if found | Excel Programming |