Solved

Correct Synatx for "cell value between" Conditional Formatting VBA

Posted on 2015-02-12
7
87 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

937 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

Need Help in Real-Time?

Connect with top rated Experts

1 Experts available now in Live!

Get 1:1 Help Now