Click here to Skip to main content
14,733,501 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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

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
Posted

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=""Total VU"")"
Selection.FormatConditions(1).Interior.ColorIndex = 30


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"",$A1=""Total VU"")"
Selection.FormatConditions(1).Interior.ColorIndex = 30
   
Thanks ryanb31, the extra tiny bit was all I needed. Thanks also to exposed, but I didn't need to get that far down the page. :)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900