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
– AntiDrondert, Commented Aug 30, 2017 at 8:54
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
– Zerk, Commented Aug 30, 2017 at 8:57
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 — phh
– phh, Commented Aug 30, 2017 at 8:59
Hi Zerk, I tried with Conditional Formatting. By not really ok for me. If you have any suggestion pls! — phh
– phh, Commented Aug 30, 2017 at 9:03

Mrig · Accepted Answer · 2017-08-30 09:25:09Z

1

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

edited Aug 30, 2017 at 9:25

answered Aug 30, 2017 at 9:06

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

phh Over a year ago

I changed Sub RowFormat() to Private Sub Worksheet_Change(ByVal Target As Range) and it's work. Thank @Mrig

Mrig Over a year ago

@PhyoHtetHlaing - See edit in answer. For Worksheet_Change you can check only for the cell changed and not the whole range.

PeterH · Accepted Answer · 2017-08-30 09:08:43Z

0

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...

answered Aug 30, 2017 at 9:08

PeterH

1,0404 gold badges16 silver badges39 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related