Colour Filling using VBA, Conditional on cell value

1
votes

I am looking to colour fill a range of cells (C2:C7) in Excel based on the the text selected in a cell (there will be a drop-down menu, two of the values being "SD" and "CS"). Here is my code so far:

Sub ChangeColor() Dim rCell As Range

With Sheet1
For Each rCell In .Range("C2:C7")

If rCell.Value <= SD Then
rCell.Interior.Color = vbRed
ElseIf rCell.Value <= CS Then
rCell.Interior.Color = vbGreen
Else: rCell.Interior.Color = vbYellow
End If
End With

End Sub

I'm told there is a compile error: End With without With

I'm just getting started in VBA so can somebody please tell me what I've done wrong?

2
excelvba
Conditional formatting is also a way to do this :D as long as you manage it properly it will not make the sheet bulky.. – bonCodigo
I can do it using conditional formatiing, however there are 5 names on the drop down list and I also want to do other things like highlight certain rows with a specific colour if a drop down name is selected. Hence I tried VBA.. any suggestions? – Clatty Cake
If you're using Excel 2007 or higher, you can do this with Conditional formatting. – Peter Albert
Cheers, will have a play! – Clatty Cake

2 Answers

5
votes

To fix your code, you need to insert a Nextstatement. before the End With line. Also, it is good practice to use indentation to visually see the different code blocks. In your case, it would look like this:

Sub YourSub
    With Sheet1
        For Each rCell In .Range("C2:C7")
            If rCell.Value <= SD Then
                rCell.Interior.Color = vbRed
            ElseIf rCell.Value <= CS Then
                rCell.Interior.Color = vbGreen
            Else
                rCell.Interior.Color = vbYellow
            End If
        Next rCell
    End With
End Sub

Also, consider to use Conditional Formatting instead of VBA here - it will do the same, but much easier!

-1
votes

I'm not sure if it is working but you can try this, because when you type in script "For commands" then you need to type next. And "For" is more used to create loops:

Sub ChangeColor()
    Dim rCell As Range
    With Sheet1
    For Each rCell In .Range("C2:C7")
        If rCell.Value <= SD Then
            rCell.Interior.Color = vbRed
        ElseIf rCell.Value <= CS Then
            rCell.Interior.Color = vbGreen
        Else rCell.Interior.Color = vbYellow
        End If
    Next
    End With
End Sub