If elseif statement Excel VBA

Question

In column A I have target production, in column B I have actual production, and in column C I have the delta between the two. I simply want to loop through each row in column C and flag each value according to a strategic bucket. The sample code I tried before going into master document is below.

My issue is surrounding how to perform the action on the current cell in the loop. The code is just coloring the last cell I clicked on instead of the cell being evaluated. Any suggestions?

Sub StratBuckets()

Dim Delta As Variant
Dim n As Integer

n = 0           
For n = 0 To 15
    ' works up to this point --> points to the right value
    Delta = Worksheets("Test").Range("A1:Z1000").Find("Start Date").Offset(n, 3).Value

    If Delta > 0 And Delta <= 10 Then
        ActiveCell.Interior.Color = vbYellow        
    ElseIf Delta > 10 Then
        ActiveCell.Interior.Color = vbGreen        
    ElseIf Delta < 0 Then
        ActiveCell.Interior.Color = vbRed                
    Else        
    End If               
Next n

End Sub

Tibo · Accepted Answer · 2017-02-07 22:58:38Z

3

You need to apply .Interior.Color on the right cell.

If Worksheets("Test").Range("A1:Z1000").Find("Start Date").Offset(n, 3) is the cell you want to colorize, then do :

Worksheets("Test").Range("A1:Z1000").Find("Start Date").Offset(n, 3).Interior.Color = ...

answered Feb 7, 2017 at 22:58

Tibo

3937 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shai Rado · Accepted Answer · 2017-02-08 07:13:04Z

Another way to go, is to Set a Range DeltaRng to the Find with:

Worksheets("Test").Range("A1:Z1000").Find("Start Date"),

then if the Find is successful, get the Delta value 3 column on the right with Delta = DeltaRng.Offset(n, 3).Value.

At last, when you are inside your If Delta > 0 And Delta <= 10 Then etc.. you can modify the .Interior.Color of the cell with DeltaRng.Offset(n, 3).Interior.Color = vbYellow.

Code

Sub StratBuckets()

Dim DeltaRng As Range
Dim Delta As Variant
Dim n As Long

For n = 0 To 15
    ' works up to this point --> points to the right value
    Set DeltaRng = Worksheets("Test").Range("A1:Z1000").Find("Start Date") '.Offset(n, 3).Value

    If Not DeltaRng Is Nothing Then '<--make sure Find was successful
        Delta = DeltaRng.Offset(n, 3).Value '<-- find the Delta value 3 columns to the right

        If Delta > 0 And Delta <= 10 Then
            DeltaRng.Offset(n, 3).Interior.Color = vbYellow
        ElseIf Delta > 10 Then
            DeltaRng.Offset(n, 3).Interior.Color = vbGreen
        ElseIf Delta < 0 Then
            DeltaRng.Offset(n, 3).Interior.Color = vbRed
        End If
    End If

Next n

End Sub

Collectives™ on Stack Overflow

If elseif statement Excel VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related