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屋!