How to change background color of cell based on other cell value by VBA

Question

I have two columns A and B in an excel sheet. A column is the drop down list that contains "Yes" and "No". And I would like to change the color of B cell that base on text value of A cell from drop down list. For example, if I select "Yes" in A1 cell than the B1 cell should show Green color. A2, A3... etc.

I am not a programmer so I am really noob at VBA coading. Conditional Formation also have a problem for this case.

If anyone have an answer for this, That would be my pleasure.

Did you atempt to do something already? If so, post your code. Try using Private Sub Worksheet_Change(ByVal Target As Range) this Sub will occur every time data in Worksheet is changed. — AntiDrondert
You say conditional formatting has a problem for this case but it should be able to do exactly what you need given proper usage. — Zerk
Sub RowFormat() Dim A As Range For Each A In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) If Not IsError(A) Then If A.Value = "Yes" Then Range("B" & C.Row & ":BB" & C.Row).Interior.ColorIndex = 6 Else Range("B" & C.Row & ":BB" & C.Row).Interior.ColorIndex = xlNone End If End If Next A End Sub — Phyo Htet Hlaing
Hi Zerk, I tried with Conditional Formatting. By not really ok for me. If you have any suggestion pls! — Phyo Htet Hlaing

Mrig Mrig · Accepted Answer · 2017-08-30T09:06:09

Made some changes in your code.

Sub RowFormat()
    Dim A As Range
    For Each A In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        If Not IsError(A) Then
            If A.Value = "Yes" Then
                A.Offset(0, 1).Interior.ColorIndex = 6
            ElseIf A.Value = "No" Then
                A.Offset(0, 1).Interior.ColorIndex = 3
            Else
                A.Offset(0, 1).Interior.ColorIndex = xlNone
            End If
        End If
    Next A
End Sub

Using Conditional Formatting.

For "Yes" use =A1="Yes",
for "No" use =A1="No" and format apply formatting accordingly.

EDIT :

If you are using Worksheet_Change event then use below code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub 'check for range
    If Target.Value = "Yes" Then 'check if yes
        Target.Offset(0, 1).Interior.ColorIndex = 6
    ElseIf Target.Value = "No" Then 'check if no
        Target.Offset(0, 1).Interior.ColorIndex = 3
    Else
        Target.Offset(0, 1).Interior.ColorIndex = xlNone
    End If
End Sub

How to change background color of cell based on other cell value by VBA

2 Answers