This is my first time in a forum. I have mostly found all the answers I could but I can't seem to find the answer for this:

I don't know if this is a bug in excel or if I am doing something wrong.

I start a new Excel application from another workbook and apply a conditional format that uses an array formula for its condition (I have read that conditional formatting always formulates in array). The conditional format doesn't apply. I need to select the cells with the conditional format, goto to the Conditional Formatting toolbar and reapply it manually. That is, I actually have to select the cells then goto Format->Conditional Format-> I see the formula applied and click OK and the conditional format works.

Why doesn't the format apply from code Am I doing something wrong Here is my code. Thanks for your help.

Dim objExcel As Application

Dim objxlWkBk As Workbook

Dim objxlSheet As Worksheet

Set objExcel = CreateObject("Excel.Application")

Set objxlWkBk = objExcel.Workbooks.Add

Set objxlSheet = objxlWkBk.Sheets(1)

objxlSheet.Range("A1").Value = 1

objxlSheet.Range("A2").Value = 2

objxlSheet.Range("A3").Value = 2

objxlSheet.Range("A4").Value = ""

objxlSheet.Range("A5").Value = 5

With objxlSheet.Range("A1:A5")

objxlSheet.Range("A1").Activate

.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(A1<>"""",SUM((A1=$A$1:$A$5)*1)>1)"

.FormatConditions(1).Interior.ColorIndex = 40

End With

objExcel.Visible = True

Set objxlSheet = Nothing

Set objxlWkBk = Nothing

Set objExcel = Nothing