Correct Synatx for "cell value between" Conditional Formatting VBA
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?
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?
ASKER
I get the error "Invalid procedure call or argument"
ASKER
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?
=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?
Can you explain/outline the criteria for your formatting?
ASKER
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
Start-Up-Score-Card-Template-Example.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
Are you getting a type mismatch error?