Conditional format cells with formula

[Villalobos]

Hello,

I would some help regarding conditional formatting with formula. My target is to change the interior color of cell (in column L until the lastrow -the original file has some thousand rows-) if the formula is turn to "true". Unfortunately I receive "Invalid procedure or argument" error msg at this line:

 .Range("L9:L" & OutputLastRow).FormatConditions.Add Type:=xlExpression, Formula1:="=AND(J9>0,K9<J9,O9>0,P9>TODAY())"

The full code is here:

Sub test()
Dim outputSheet4 As Worksheet
Dim OutputLastRow As Long

Set outputSheet4 = Worksheets("Evaluation")

  With outputSheet4
  OutputLastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
  .Range("L9:L" & OutputLastRow).FormatConditions.Delete
  .Range("L9:L" & OutputLastRow).FormatConditions.Add Type:=xlExpression, Formula1:="=AND(J9>0,K9<J9,O9>0,P9>TODAY())"
  .Range("L9:L" & OutputLastRow).FormatConditions(1).Interior.Color = RGB(255, 0, 0)
  .Range("L9:L" & OutputLastRow).FormatConditions(1).Font.Color = RGB(255, 255, 255)
  .Range("L9:L" & OutputLastRow).FormatConditions(1).Font.Bold = True
  End With
 End Sub

Could somebody give me some advice how to solve this problem?

I attached the sample file as well.



Thanks in advance the help!

 

[NARAYANK991]

Hi ,

I don't get an error ; can you see this file ?

Narayan

 

[Villalobos]

Hi Narayan,

It is strange, on my side the problem still open. Maybe I have some customizing problem with XL?

PS: Do you know any other way to do the conditional formatting by vba?

 

[NARAYANK991]

Hi ,

What is the version of Excel you are using ?

Narayan

 

[NARAYANK991]

Hi ,

Is your separator a comma or a semi-colon ?

Narayan

 

[Villalobos]

Hi,

I use XL2013 and my separator is comma.