CODEWITHSUNDEEP
excelvba
phh
phh

Reputation: 149

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

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.

Upvotes: 2

Views: 7844

Answers (2)

Mrig
Mrig

Reputation: 11727

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.

enter image description here

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

Upvotes: 1

PeterH
PeterH

Reputation: 1040

To use conditional formatting

Select Column B, Click Conditional Format >

Highlight Cells Rules >

Equal Too > Type "Yes"

On the drop down to the right select Custom,

choose your formatting,

Repeat the process again for "No"

There will be many easier to follow methods shown with a quick google search...

Upvotes: 0

Related Questions