Adding a format condition in Excel through Access VBA

Question

So currently i got these lines in my code,

.Range("C2").Formula = "=IF(B2 = """"," & """REQUIRED""" & "," & """""" & ") "
.Range("C3").Formula = "=IF(B3 = """"," & """REQUIRED""" & "," & """""" & ") "

my goal was to add a conditional formatting to it, so if it equal to Required from the range =$C$2:$C$3 turn background red so far ive seen some codes online but none of them seem to apply. I modified some existing code for my purposes by i get an error on xlExpression "variable not defined". Can anyone help me?

.Range("C2:C3").Selection.FormatConditions.Delete 
.Range("C2:C3").Selection.FormatConditions.Add TYPE:=xlExpression, Formula1:="=(=$C$2:$C$23 = 'REQUIRED')"

try recording a macro as you apply the conditional formatting manually. This should provide the syntax you need to get to solve your riddle :) — Scott Holtzman
– Scott Holtzman, Commented Oct 29, 2012 at 16:25
I've gotten the code however @ScottHoltzman do you remember my previous question i am adding this within the SUB, and i am still getting the error "variable not defined" on the xlExpression — KSM
– KSM, Commented Oct 29, 2012 at 16:50
If it is coded in MS Access, do you have a reference to the Excel library? If not, you will have to substitute the constants for values. For example, xlExpression = 2 — Fionnuala
– Fionnuala, Commented Oct 29, 2012 at 17:12
Range is a property of an object, but xlExpression is a built-in constant, so you can refer to one without a library, but not the other. — Fionnuala
– Fionnuala, Commented Oct 29, 2012 at 17:22
@Remou is right. I remember from your previous post that you used late binding so you will need to use the values instead of the constant. For more on early vs. late binding, see this. If you use early binding, you'll be able to use the constants, because you are setting a reference directly to the XL application library. It would also be code to post a link to your other question, so others viewing this will be able to follow in the future. — Scott Holtzman
– Scott Holtzman, Commented Oct 29, 2012 at 17:22

pr42 · Accepted Answer · 2018-01-16 11:54:36Z

0

You have to replace xlExpression with 2.

answered Jan 16, 2018 at 11:54

pr42

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding a format condition in Excel through Access VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related