使用VBA对一系列单元格应用条件格式

时间:2022-10-29 20:25:28

I would like to know how to access the column in conditional formatting titled 'Applies To' and input my own conditions. I have included a screenshot for better reference.

我想知道如何访问标题为“apply to”的条件格式列,并输入我自己的条件。我已经包括了一个截图,以更好的参考。

使用VBA对一系列单元格应用条件格式

My code for adding the syntax in conditional formatting is,

我在条件格式中添加语法的代码是,

With Selection
  .FormatConditions.Delete
  .FormatConditions.Add Type:=xlExpression, Formula1:="=" & c.Address & "=TRUE"
  .
  .
  .
End With

I believe the code should be added in there but i just cannot find the correct syntax.

我认为应该在那里添加代码,但是我找不到正确的语法。

Update :

更新:

I updated my code to look like this,

我更新了我的代码,

With Range(Cells(c.Row, "B"), Cells(c.Row, "N"))
  .FormatConditions.Delete
  .FormatConditions.Add Type:=xlExpression, Formula1:="=" & c.Address
  .FormatConditions(1).Interior.ColorIndex = 15 'change for other color when ticked
End With

This would essentially make rows of a specific range relevant to where i placed the checkbox, have their background colour changed. The checkbox position is represented by c.Address where 'c' contains the location of the cell that i selected to place my checkbox.

这实际上会使一个特定范围的行与我放置复选框的位置相关,使它们的背景颜色发生变化。复选框位置由c表示。地址,其中'c'包含我选择放置复选框的单元格的位置。

2 个解决方案

#1


8  

You need to do something like this (Range("A25") is exactly what you are going to find):

你需要做这样的事情(Range(“A25”)就是你要找到的):

With Range("A25")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, _
            Formula1:="=" & c.Address 
        '.
        '.
        '.
End With

and there is no need to write "=" & c.Address & "=TRUE", you can use just "=" & c.Address.

没有必要写"=" & c。地址&“=TRUE”,您可以使用“=”& c.Address。

#2


5  

The "applies to" is inherent in the Selection that the With block is performed on.

“apply to”是在对With块执行的选择中固有的。

#1


8  

You need to do something like this (Range("A25") is exactly what you are going to find):

你需要做这样的事情(Range(“A25”)就是你要找到的):

With Range("A25")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, _
            Formula1:="=" & c.Address 
        '.
        '.
        '.
End With

and there is no need to write "=" & c.Address & "=TRUE", you can use just "=" & c.Address.

没有必要写"=" & c。地址&“=TRUE”,您可以使用“=”& c.Address。

#2


5  

The "applies to" is inherent in the Selection that the With block is performed on.

“apply to”是在对With块执行的选择中固有的。