?
Solved

Correct Synatx for "cell value between" Conditional Formatting VBA

Posted on 2015-02-12
7
Medium Priority
?
105 Views
Last Modified: 2015-02-13
I've the below code choking on me. I'm trying to get "cell value between" conditional formatting using VBA. In the Conditional Formatting Rules Manager the rule looks like:

Cell value between $C$6 + $D$6 + 21 and $C$6 + $D$6 + 21 + 'Scoring Criteria'!$B$22

I get the error "Invalid procedure call or argument" with either:

.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OR('Per Run Eval - #" & intRunNumber + 1 & "'!J6 between C6+D6+21 and C6+D6+21+'Scoring Criteria'!$B$22)"

or

.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OR('Per Run Eval - #" & intRunNumber + 1 & "'!J6>C6+D6+21 and 'Per Run Eval - #" & intRunNumber + 1 & "'!J6<C6+D6+21+'Scoring Criteria'!$B$22

What is the correct syntax?
0
Comment
Question by:Michael Vasilevsky
  • 4
  • 2
7 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 40606335
How is it 'choking'?

Are you getting a type mismatch error?
0
 
LVL 10

Author Comment

by:Michael Vasilevsky
ID: 40606340
I get the error "Invalid procedure call or argument"
0
 
LVL 10

Author Comment

by:Michael Vasilevsky
ID: 40606350
Ok I also notice working in the Formatting Rules Manager the following resolves correctly:

=OR('Per Run Eval - #2'!M6='Scoring Criteria'!$A$26, 'Per Run Eval - #2'!N6>'Scoring Criteria'!$A$30)

but the following does not:

=OR('Per Run Eval - #2'!M6='Scoring Criteria'!$A$26, 'Per Run Eval - #2'!N6>2+'Scoring Criteria'!$A$30)

Do I need to do my calculations outside of the conditional formatting rules?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:Norie
ID: 40606379
Can you explain/outline the criteria for your formatting?
0
 
LVL 10

Author Comment

by:Michael Vasilevsky
ID: 40606407
Probably easier to look at the workbook. "Campaign Overview" rows 20 through 31 is where I'm trying to get formatting. Those cells are based on "Per Run Eval #1" columns J through O.
Start-Up-Score-Card-Template-Example.xls
0
 
LVL 34

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 40607565
You need to put the bit that you are comparing with in brackets and the AND function needs to go at the beginning:

.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND('Per Run Eval - #" & intRunNumber + 1 & "'!J6>(C6+D6+21),'Per Run Eval - #" & intRunNumber + 1 & "'!J6<(C6+D6+21+'Scoring Criteria'!$B$22))

It could also be getting confused as to where the Formula is finishing; might need some double double quotes to allow for mix of hard coded text and variable.
0
 
LVL 10

Author Comment

by:Michael Vasilevsky
ID: 40608019
Mr. Henson: I think that might be it. THIS is why I'd rather just be able to format on another cells color.

Also you experts didn't catch that I need to fully specify the cell, i.e.

 "=AND('Per Run Eval - #" & intRunNumber + 1 & "'!J6>('Per Run Eval - #" & intRunNumber + 1 & '"!C6+...

:-)

Thanks again for your help on this - you are the man!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question