change color of a cell, depending on value

[robertvdb]

I have a sheet where the color of cells A3:A5 should change, depending on the value. I don't want to work with conditional formatting, so I wrote the VBA code below. However, all cells stay in RED, no matter which value.

What is wrong with my VBA ? See also the image.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim col_A_rng As Range
Set col_A_rng = Range("A3:A5")

If Target.Count = 1 And Not Application.Intersect(Target, col_A_rng) Is Nothing Then
    If Target.Value = 0 Then Target.Interior.Color = RGB(255, 0, 0)
    ElseIf Target.Value = 1 Then Target.Interior.Color = RGB(0, 255, 0)
    Else
End If

End Sub

 

[Georgiboy]

Try as below, i have swapped the IF statements for a select case:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col_A_rng As Range
    
    Set col_A_rng = Range("A3:A5")
    
    If Target.Count = 1 And Not Intersect(Target, col_A_rng) Is Nothing Then
        Select Case Target.Value
            Case 0
                Target.Interior.Color = vbRed
            Case 1
                Target.Interior.Color = vbGreen
        End Select
    End If
End Sub

 

[robertvdb]

Thanks, your solution works fine !

I am still wondering what can be wrong with my IF statements...

 

[Georgiboy]

I don't think the 'Else' were needed, i think the Ifs would have worked as below:

If Target.Count = 1 And Not Application.Intersect(Target, col_A_rng) Is Nothing Then
    If Target.Value = 0 Then Target.Interior.Color = RGB(255, 0, 0)
    If Target.Value = 1 Then Target.Interior.Color = RGB(0, 255, 0)
End If

Sometimes i think select case is easier to read and add to in some instances.

 

[robertvdb]

Sir, I think you're a genius ! Thank you so much !

 

[Georgiboy]

You are welcome, thanks for the feedback.