Review VBA code? What is this in English?

Could I have someone review this code?  It just looked odd not having the Result =  in each case following Case statements after the original Case Is?  Would like to know what this is saying in English.

Range("D2").Activate
    Select Case ActiveCell.Value
    
        Case Is > 1.15 * IEHaworthLogin
            Result = 1.15 * IEHaworthLogin
            ActiveCell.Interior.Color = vbRed
        Case 1.1 * IEHaworthLogin To 1.15 * IEHaworthLogin
            ActiveCell.Interior.Color = vbYellow
        Case 0.9 * IEHaworthLogin To 1.1 * IEHaworthLogin
            ActiveCell.Interior.Color = vbGreen
        Case Is < 0.9 * IEHaworthLogin
            ActiveCell.Interior.Color = vbCyan
    End Select

Open in new window

RWayneHAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
The first one that's true is calculated and the following ones are ignored.
Range("D2").Activate
    Select Case ActiveCell.Value ' Examine the contents of D2
    
        Case Is > 1.15 * IEHaworthLogin
           ' It's greater than 1.15 * IEHaworthLogin (I assume that this is some kind of numeric variable)
            Result = 1.15 * IEHaworthLogin
            ActiveCell.Interior.Color = vbRed ' Make the cell red
        Case 1.1 * IEHaworthLogin To 1.15 * IEHaworthLogin
             'The value is between 1.11 * IEHaworthLogin and 1.15 * IEHaworthLogin
            ActiveCell.Interior.Color = vbYellow
        Case 0.9 * IEHaworthLogin To 1.1 * IEHaworthLogin
           'The value is between 0.9 * IEHaworthLogin and 1.11 * IEHaworthLogin
            ActiveCell.Interior.Color = vbGreen
        Case Is < 0.9 * IEHaworthLogin
          ' 0.9 * IEHaworthLogin value is < .9 so make it cyan color
            ActiveCell.Interior.Color = vbCyan
    End Select

Open in new window

Fabrice LambertConsultingCommented:
In short:

Select the D2 cell.
If the cell's value is above 1.15 multiplied by IEHaworthLogin:
- Store 1.15 multiplied by IEHaworthLogin for later use.
- Turn the cell's color to red.

If the cell's value is between 1.1 * IEHaworthLogin and  1.15 * IEHaworthLogin:
- Turn the cell's color to yellow

If the cell's value is between 0.9 * IEHaworthLogin and 1.1 * IEHaworthLogin:
- turn the cell's color to green

If the cell's value is below 0.9 * IEHaworthLogin:
- turn the cell's color to cyan

And what IEHaworthLogin mean ? No clue ....

Side notes:
- Most if this code could be replaced by conditional formatting rules.
- It sux !
Activating cells is slow and disturb the user.
Using the ActiveCell object provide zero garantees.
aikimarkCommented:
1. I recommend deleting the Activate statement and changing the Select Case statement to the following:
    Select Case CDbl(Range("D2").Value)

Open in new window

or
    Select Case CSng(Range("D2").Value)

Open in new window

2. There is always a chance that the results might not be what you expect because of floating point arithmetic and representation.
3. What is the data type of the IEHaworthLogin variable?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
Martin LissOlder than dirtCommented:
It doesn't make sense to have Result = in the first Case or anywhere (if similar statements were added)  because Result is being set to what the value already is and you could use Range("D2").Value directly.
RWayneHAuthor Commented:
Looks like the data type in the variable is General... and it is an average.  I was at first concerned about the E+02 type values in the average variable but it is not there anymore.  I prefer the If and ElseIf method myself...  The sub is only checking a couple cells, so it is not a huge deal.. and I did not want to go down the Conditional Formatting path.

There was a comment by Martin that said "rest are ignored"?  Is that ok?  As long as Result is calculated once it is good? Or should that be in each of the follow Cases too?  I guess I am not used to the Case Is and Case method instead of the If and ElseIf
RWayneHAuthor Commented:
How would that look replacing the code with:  Range("D2").Value directly?  Or using the If and IfElse method instead of CaseIs and Case?
Martin LissOlder than dirtCommented:
I have no idea what Result is used for but MsgBox Range("D2").Value would show the same value that's in Result.

IMO, If and IF Else would be a poorer way to go.
Martin LissOlder than dirtCommented:
There was a comment by Martin that said "rest are ignored"?  Is that ok?
They are only ignored in the sense that once VBA finds a Case that's True the others aren't looked at, and I prefer Select Case over If's for that reason, because with If's every If is evaluated even if previous ones are True and that takes up unnecessary processing time (even if it's minuscule).
Bill PrewIT / Software Engineering ConsultantCommented:
Based on the comments I think this is where I would end up.  Basically if the value is > 1.15 * IEHaworthLogin then Result is set to that, and the color is red.  The other parts of the case just set the cell to different colors depending on values.

I would mention that some boundary values like 1.1 * IEHaworthLogin are somewhat ambiguous currently, since they are technically included in two ranges.  So the first match will be taken, which may be okay for what you want...

    Select Case CDbl(Range("D2").Value)
        Case Is > 1.15 * IEHaworthLogin
            Result = 1.15 * IEHaworthLogin
            ActiveCell.Interior.Color = vbRed
        Case 1.1 * IEHaworthLogin To 1.15 * IEHaworthLogin
            ActiveCell.Interior.Color = vbYellow
        Case 0.9 * IEHaworthLogin To 1.1 * IEHaworthLogin
            ActiveCell.Interior.Color = vbGreen
        Case Is < 0.9 * IEHaworthLogin
            ActiveCell.Interior.Color = vbCyan
    End Select

Open in new window

»bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RWayneHAuthor Commented:
Thanks all for the help today....  guess I need to start writing more Case Is \ Case statements then If and IfElse...   Appreciate the help.
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
RWayneHAuthor Commented:
Maybe I should add this note, that I had to remove the ActiveCell references too.

Based on this the final code looks like:
Select Case (Range("D2").Value)
    Case Is > 1.15 * IEHaworthLogin
        Result = 1.15 * IEHaworthLogin
        Range("D2").Interior.Color = vbRed
    Case 1.1 * IEHaworthLogin To 1.15 * IEHaworthLogin
        Range("D2").Interior.Color = vbYellow
    Case 0.9 * IEHaworthLogin To 1.1 * IEHaworthLogin
        ActiveCell.Interior.Color = vbGreen
    Case Is < 0.9 * IEHaworthLogin
        Range("D2").Interior.Color = vbCyan
End Select

Open in new window

Martin LissOlder than dirtCommented:
Do you need line 3?
RWayneHAuthor Commented:
Thats right...  I did not need Ln3.  Thanks for pointing that out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.