Conditional Formatting in Excel VBA

Question

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:

VB

'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 21-Jun-12 0:41am

the992kid

Add a Solution

3 solutions

Solution 2

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:

VB

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:

VB

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

Posted 21-Jun-12 2:06am

exposed12

Solution 3

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. :)

Posted 21-Jun-12 3:01am

the992kid

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

ZurdoDev · Accepted Answer · 2012-06-21T01:54:00

Solution 1

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

Posted 21-Jun-12 1:54am

ZurdoDev