1

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')"
Alex K.
  • 171,639
  • 30
  • 264
  • 288
KSM
  • 262
  • 2
  • 6
  • 16
  • 2
    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 Oct 29 '12 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 Oct 29 '12 at 16:50
  • the code generated when recorded Range("C2").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$C$2=""REQUIRED""" – KSM Oct 29 '12 at 16:53
  • What does this have to do with Access? If nothing, please remove the Access tags. – Fionnuala Oct 29 '12 at 17:01
  • @Remou its coded in Access 2007, should it still not be in access ??? – KSM Oct 29 '12 at 17:08
  • 1 thing I can think of is to make sure the version of the XL application that you are opening in your Access database is compatible with the syntax `Type:=xlExpression` If you are running XL2003, it may very well not be. – Scott Holtzman Oct 29 '12 at 17:09
  • 1
    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 Oct 29 '12 at 17:12
  • I'm running on the MS 2007 access and excel, i am assuming the reference to the Excel library was automated since i was able to do other excel methods as "Range("C2").Formula " and "value" if not i will look into that. – KSM Oct 29 '12 at 17:18
  • 3
    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 Oct 29 '12 at 17:22
  • 2
    @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](http://www.dicks-clicks.com/excel/olBinding.htm). 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 Oct 29 '12 at 17:22
  • @Remou and Scott Holtzman thank you for the help! and to anyone reading this the link to my previous question which is @ http://stackoverflow.com/a/13123588/1090656 – KSM Oct 29 '12 at 17:47

1 Answers1

0

You have to replace xlExpression with 2.

pr42
  • 1