彩色细胞基于其价值--VBA [英] Color cells based on their value - VBA

问题描述

我需要能够根据VBA中的值对代码单元进行颜色编码，因为条件格式化将不会处理最终需要的条件数。例如，如果B的值为Decommission，那么我想检查C，D和E的值，并将它们与B的值相关联。不幸的是，我写的代码正在整个运行基于第一个值的单位和颜色编码范围内的所有内容。我已经定义了2 x范围（all_data和响应）我知道什么是错误，但我不知道如何告诉代码只将格式限制在行的值。任何帮助将不胜感激。

I need to be able to colour code cells based on their values in VBA as conditional formatting will not handle the number of conditions I will ultimately require. For instance if the value of B is "Decommission" then I would like to check the values of C, D and E and colour code them in relation to the value of B. Unfortunately, the code I've written is running through the whole sheet and colour coding everything in the range based on the first value. I've defined 2 x ranges (all_data and response) I know what's wrong but I don't know how to tell the code to only restrict the formatting to the value of the row.. Any help would be greatly appreciated.

Sub Formatter()

Dim All_Data As Range
Dim Response As Range

Dim MyCell As Range
Dim MyCell2 As Range

Set All_Data = Range("All_Data")
Set Response = Range("Response")

For Each MyCell In All_Data
If MyCell.Value = "Decommission" Then
   MyCell.Interior.ColorIndex = 3
         For Each MyCell2 In Response
            If MyCell2.Value = "Yes" Then
                MyCell2.Interior.ColorIndex = 4
            End If
         Next
End If
Next

End Sub

推荐答案

尝试这样：

Sub Formatter()

Dim All_Data As Range
Dim Response As Range

Dim MyCell As Range
Dim MyCell2 As Range

Set All_Data = Range("All_Data")
Set Response = Range("Response")

For Each MyCell In All_Data
If MyCell.Value = "Decommission" Then
   MyCell.Interior.ColorIndex = 3
         For Each MyCell2 In Intersect(Response, ActiveSheet.Rows(MyCell.Row))
            If MyCell2.Value = "Yes" Then
                MyCell2.Interior.ColorIndex = 4
            End If
         Next
End If
Next

End Sub

这篇关于彩色细胞基于其价值--VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！