VBA Entering If statement

[Peteor]

This one is beating me..... I have a macro on the sheet where anytime a change is made, it enters this sub. That sub works perfectly. The sub below is my problem. The sub below is highlighting cells with ColorIndex = xlNone with ColorIndex = 42

I cannot figure out why it is entering that If statement. Anyone have any guidance here?

Sub Date_Highlights(Rng As Range)


If Rng.Interior.ColorIndex = xlNone Then
Rng.Interior.ColorIndex = 46
Else
End If


If Rng.Interior.ColorIndex = 46 Then
Rng.Interior.ColorIndex = 4
Else
End If


If Rng.Interior.ColorIndex = 4 Then
Rng.Interior.ColorIndex = 42
Else
End If


End Sub

 

[Joe4]

Because the way you have written it, it is going through all three IF statements!

If if it "xlNone", the first If statement is changing it to 46.
But then your next If checks to see if it is 46, then changing it to 4.
And then your last If checks to see if it is 4, and changes it to 42.
So that is how you are ending up there. I don't think you want it to pass through all 3 If statements as it is changing the value on the fly.

You can make the second 2 IF "ELSE" statements, or you can use the CASE statement instead (which I like better), i.e.

[COLOR=#333333]Sub Date_Highlights(Rng As Range)[/COLOR]

[COLOR=#333333]    Select Case Rng.Interior.ColorIndex 
        Case xlNone
[/COLOR]            R[COLOR=#333333]ng.Interior.ColorIndex = 46
[/COLOR]        Case [COLOR=#333333]46 
            [/COLOR][COLOR=#333333]Rng.Interior.ColorIndex = 4
[/COLOR]       Case 4
[COLOR=#333333]            Rng.Interior.ColorIndex = 42[/COLOR]
[COLOR=#333333]     End Select[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

Note: I am not sure if "xlNone" should have quotes around it on or not. Try both ways and see which one works.

 

[Fluff]

If the cell has no fill colour you make it 46, you then check to see if it's 46 (which it will be as you just changed it) & then change it to 4.
Lastly you check to see if it's 4 (see point above) & then change it to 42
Try

If Rng.Interior.ColorIndex = xlNone Then
   Rng.Interior.ColorIndex = 46
ElseIf Rng.Interior.ColorIndex = 46 Then
   Rng.Interior.ColorIndex = 4
ElseIf Rng.Interior.ColorIndex = 4 Then
   Rng.Interior.ColorIndex = 42
End If

 

[Peteor]

Your code work perfectly as written. I would have never got it haha!!! I do see now though... It was procedurally changing the interior color through all 3 if statements at VBA speed, so I was not seeing it. Also, I was not able to "Step In" as it runs from a script on the sheet. First time I have ever even seen the "Select Case" method. Thank you so much!!

 

[Peteor]

Thank you Fluff! I used Joe4's code as it was something new for me to try. I do see how your code would have solved the problem though! Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.
If you ever need to step through code like that, put the Stop as the first line.
That way when the code is called it will stop at that line & you can then use F8 to step through it.

 

[Peteor]

I See!!! I didn't think to try that! Thank you!

 

[Fluff]

My pleasure