Why do I get random/wrong output from VBA Select Case?

Question

I have a problem with the code below. I've just started to learn this and when I did a simple exercise, it turned out the code is working really randomly.

Sometime nothing is done, sometime only one case is being selected but never the right formatting is applied. I'm quite confused.

Could you please help on this one? ;)

Sub Colors()

Dim Check As String
Check = ActiveCell.Value
Range("A2").Select

Do While ActiveCell.Value <> ""

    Select Case Check
    Case "Red"
        ActiveCell.EntireRow.Interior.Color = RGB(200, 100, 100)
    Case "Blue"
        ActiveCell.EntireRow.Interior.Color = RGB(100, 100, 200)
    Case "Green"
        ActiveCell.EntireRow.Interior.Color = RGB(100, 200, 100)
    End Select

    ActiveCell.Offset(1, 0).Select

Loop

End Sub

Highly suggest you give [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) a read. — BigBen, Nov 20 '19 at 19:12
It all depends on exactly what your data is on the worksheet and what you expect. Can you provide an example of your data? — PeterT, Nov 20 '19 at 19:14
What is the `ActiveCell` address when this procedure is called, and what is its value? Note that `Check` is constant for every iteration of the loop... — Mathieu Guindon, Nov 20 '19 at 19:26
Right now, this code is getting the value of whatever cell you happen to have selected when you start running it, and storing it in `Check`. Then, starting with cell A2, it's coloring the cells of column A with a color, depending on the value that came from that initially selected cell. Once it hits a cell that has a value, it stops. Is all that what you mean to happen? — Josh Eller, Nov 20 '19 at 19:29
The pattern in the cells (their value) counting from the cell A2 down is: Red Blue Green. What I wanted: if e.g the cell in A5 says Green the entire row 5 should go green. If the cell in A28 says Blue, the entire row 28 should go blue, etc. If I change the Select Case to a bunch of Elseif, it works fine. — JakubTracz, Nov 20 '19 at 20:45
The point is that your code is doing exactly what you wrote the code to do. We're less clear about what you **want** the code to do. What is the data? What result do you want to see, based on the data and the active cell? — PeterT, Nov 20 '19 at 20:47

score 1 · Accepted Answer · answered Nov 20 '19 at 19:29

1

Check in constant for every iteration of the loop, so Select Case Check always evaluates to the same thing, and the outcome of the execution of this procedure depends solely on the value of whatever cell happened to be the ActiveCell when the procedure was invoked.

Avoid Select and Activate, but to fix your current immediate problem, you'll want to replace Check with ActiveCell here, and maybe explicitly invoke its Value member:

Select Case ActiveCell.Value

The Check assignment becomes redundant, and now the loop will successively activate a cell starting in A2, evaluate its content, and set the interior color accordingly.

This could also be achieved without any VBA code, using conditional formatting rules applied to the entire row. Consider using conditional formatting instead.

answered Nov 20 '19 at 19:29

Mathieu Guindon

69,817
8
107
235

Thank you very much for this reply. I understand your point and checked - it works, however I don't understand why the `Check` variable with the `ActiveCell.Value` assigned to it gives a different output than the plain `ActiveCell.Value` used within the `Select Case`. – JakubTracz Nov 20 '19 at 21:27
@JakubTracz `Check` is assigned once, before the loop, and then never reassigned again: it's still holding the exact same cell reference at every iteration - `Check` isn't magically going to "keep up" with what the `ActiveCell` is while the body of the loop constantly activates a different cell. Don't do this to yourself - avoid Select/Activate and you won't have to deal with this kind of issues. – Mathieu Guindon Nov 20 '19 at 21:34

Why do I get random/wrong output from VBA Select Case?

1 Answers1