CODEWITHSUNDEEP
vbaexcel
David Kris
David Kris

Reputation: 661

Change range color depending on cell value VBA

I'm trying to have my code work so if the user types a "1" inside cell D19 then the background colors of cells F2 and F3 change to red and if they type a "2" the color changes to yellow. I can't see why my code isn't working, anything helps. Thanks.

Sub ColorColumns()
   Dim r1 As Range, r2 As Range
      Set r1 = Range("D19")
      Set r2 = Range("F2:F3")
      If r1.Value = 1 Then r2.Interior.Color = vbRed
      If r1.Value = 2 Then r2.Interior.Color = vbYellow
End Sub

Upvotes: 1

Views: 4894

Answers (2)

Ricardo González
Ricardo González

Reputation: 1423

Conditional formating will be better, this way Excel will modify F2:F3 background colors each time the user types in D19 cell

Sub ColorColumns()
    Dim r As Range

    Set r = Range("F2:F3")

    r.FormatConditions.Delete

    r.FormatConditions.Add Type:=xlExpression, Formula1:="=$D$19=1"
    r.FormatConditions(r.FormatConditions.Count).SetFirstPriority
    With r.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 244
        .TintAndShade = 0
    End With
    r.FormatConditions(1).StopIfTrue = False

    r.FormatConditions.Add Type:=xlExpression, Formula1:="=$D$19=2"
    r.FormatConditions(r.FormatConditions.Count).SetFirstPriority
    With r.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    r.FormatConditions(1).StopIfTrue = False
End Sub

Notice the condition "=$D$19=2" in r.FormatConditions.Add Type:=xlExpression, Formula1:="=$D$19=2"

Upvotes: 2

CallumDA
CallumDA

Reputation: 12113

I would also recommend using conditional formatting. However, to answer the question as asked: put this into the worksheet module of the worksheet that has these ranges on.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D19")) Is Nothing Then
        Select Case Range("D19").Value
            Case 1
                Range("F2:F3").Interior.Color = vbRed
            Case 2
                Range("F2:F3").Interior.Color = vbYellow
        End Select
    End If
End Sub

I've tested this code and it's working well. If you would like to remove the colouring if the value of D19 is neither 1 or 2 then you can include a Case Else statement too.

Upvotes: 3

Related Questions