Solved

Correct Synatx for "cell value between" Conditional Formatting VBA

Posted on 2015-02-12
7
88 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 33

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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 33

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 32

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

776 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