Conditional Formatting Problem/Bug?
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
