Solved

Correct Synatx for "cell value between" Conditional Formatting VBA

Posted on 2015-02-12
7
94 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
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 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 500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

737 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