?
Solved

Correct Synatx for "cell value between" Conditional Formatting VBA

Posted on 2015-02-12
7
Medium Priority
?
97 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 34

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 34

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 33

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

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!

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

765 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