Excel VBA中的条件格式 [英] Conditional Formatting in Excel VBA

问题描述

大家好.

好的，所以我尝试通过VBA在Excel中插入新行，然后应用3条条件格式设置规则集.规则1和2很简单，因为它们查找vlaue(分别为1和2)并相应地应用格式；第三条规则使用的公式在Excel中看起来像这样:

= OR(V12 ="DP"，V12 ="IG"，V12 ="MG"，V12 ="PASE"，V12 ="FOC"，V12 ="UP"，V12 ="OSD").我之所以使用VBA，是因为我希望单元格引用成为变量，而不是像V12那样卡住.

到目前为止，我的代码是:

Hi again everyone.

Okay, so I am trying to insert a new row in Excel via VBA and then apply a set of 3 conditional formatting rules. Rules 1 and 2 are simple, in that they look for a vlaue, (1 and 2 respectively) and apply the formatting accordingly; the 3rd rule uses a formula that in Excel proper looks like this:

=OR(V12="DP",V12="IG",V12="MG",V12="PASE",V12="FOC",V12="UP",V12="OSD"). I am using VBA because I want the cell references to be variables rather than stuck as V12.

The code I have so far is this:

'Identify selection
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=OR(" & ActiveCell & "=""DP""," & ActiveCell & "=""IG""," & ActiveCell & "=""MG""," & ActiveCell & "=""PASE""," & ActiveCell & "=""FOC""," & ActiveCell & "=""UP""," & ActiveCell & "=""OSD"")"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority  'and so on to set the formatting

在运行时，宏到达"Selection.FormatConditions ..."行，并返回错误运行时错误" 5":无效的过程调用或参数".

我从宏记录器获取了代码-我所做的只是用"ActiveCell"替换了绝对单元格引用.我想这就是问题所在，但我尝试使用范围变量进行了尝试，但无论如何都无法工作-我在网上找不到任何有用的方法，无所不能..

问候

Jimmy

On run, the macro gets to the ''Selection.FormatConditions...'' line and returns the error ''Run time error ''5'': Invalid procedure call or argument''.

I got the code from the macro recorder -all I did was replace the absolute cell refs with ''ActiveCell''. I guess this is where the problem is, but I tried it with a range variable and that didn''t work either - I can''t find anything helpful on the net, anything you can do..?

Regards

Jimmy

推荐答案

您需要ActiveCell的地址，而不是对象ActiveCell.尝试改用ActiveCell.Address.

You need the ActiveCell''s address, not the object ActiveCell. Try using ActiveCell.Address instead.

我尝试了一下，我认为问题出在您的公式中是您的''&''，当我删除它们时，它对我有用.

对我有用的是在活动单元格中使用字符串变量:

Hi,
i tried this out and i think the problem are your ''&'' within your formula, it worked for me when i deleted them.

What worked for me was using a string variable for the active cell:

Dim ActiveCell As String
ActiveCell = Excel.ActiveCell

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(ActiveCell=""Total DG"",

A1 = "总VU")" Selection.FormatConditions( 1 ).Interior.ColorIndex = 30

A1=""Total VU"")" Selection.FormatConditions(1).Interior.ColorIndex = 30

并使用仅包含具有手动给定单元格名称的String的变量:

and also using a variable holding just a String with the manually given cell name:

Dim ActiveCell As String
ActiveCell = "A1"

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(ActiveCell=""Total DG"",

这篇关于Excel VBA中的条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！