如何通过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.

我在excel表中有两列A和B.列是包含“是”和“否”的下拉列表。我想从下拉列表中更改基于A单元格文本值的B单元格的颜色。例如，如果我在A1单元格中选择“是”，则B1单元格应显示绿色。 A2，A3 ......等

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

我不是程序员所以我在VBA coading时真的很棒。对于这种情况，条件形成也存在问题。

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

如果有人对此有答案，那将是我的荣幸。

2 个解决方案

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

对于“是”，使用= A1 =“是”，对于“否”使用= A1 =“否”，格式相应地应用格式。

EDIT :

编辑：

If you are using Worksheet_Change event then use below code.

如果您使用的是Worksheet_Change事件，请使用下面的代码。

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

#2

To use conditional formatting

使用条件格式

Select Column B, Click Conditional Format >

选择B列，单击条件格式>

Highlight Cells Rules >

突出显示单元格规则>

Equal Too > Type "Yes"

等于>输入“是”

On the drop down to the right select Custom,

在下拉菜单右侧选择Custom，

choose your formatting,

选择你的格式，

Repeat the process again for "No"

再次重复此过程为“否”

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

使用快速谷歌搜索会显示许多更容易理解的方法...

#1