Conditional Formatting Problem/Bug?

FaV1105

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.

Code Snippet

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

Cringing Dragon

I don't think you're doing anything wrong. I was easily able to replicate the same (on Excel 2000, so assuming you're using something more recent this is not a new phenomenon). The VBA code assigns the conditional format formula and formats, but it doesn't seem to actually apply them until you interactively apply the conditional format.

It appears to be related to the array formula in the conditional format. If you replace it with a non-array formula then it works fine. For example, the following non-array equivalent to your formula works OK:

Formula1:="=AND(A1<>"""",COUNTIF($A$1:$A$5,A1)>1)"

That doesn't solve the underlying problem of how to apply array formulae in conditional formatting via VBA. I tried a few things to try to make it refresh or redraw, without success. Even applying a second conditional format applies the second format but doesn't "activate" the first. I think it's likely that this is a bug.

FaV1105

Cringing Dragon, Thank you for your prompt reply.

Yes, what you suggested does not solve the underlying problem but you have given me an alternative solution to my problem. I couldn't think of others ways to give me the same result and the COUNTIF function did that for me so thank you so very much. It is really appreciated.

I am still curious why the SUM function didn't work the way from VBA but does when you do it manually.

Thanks again.

Adam D. Turner

It should work fine.

It seems like you're not applying the .Select property to let the code know how or where to apply the code.

Try

objxlSheet.Range("A1:A5").Select

Then all your with block

Adam

FaV1105

Adam, I tried adding the .select code you suggested before the with block and it still doesn't work. Am I doing something wrong

Adam D. Turner

Ok I'm confused. I ran the code you provided and it worked fine.

Which version of Office are you using I tested your code on 2003.

Adam

FaV1105

Adam, I am using 2003 as well. Cringing Dragon had the same problem as me. Not sure what the problem is.

Cringing Dragon has given me a work-around to my problem so I guess my curiousity will have to be satisfied at a later time. For now I thank you both for your help.

Cringing Dragon

I realise FaV1105's happy with the solution, but in case anyone is interested in dealing with it as a bug, I've just tried on an Office Pro 2003 machine (XP Pro OS), and I still get the same behaviour as FaV1105 reported in the original post, and the same as I got on Excel 2000. There are few, if any, similarities between the 2 machines I've tried this on (the first was Office Pro 2000 under Vista BE - a strange combination I know). Completely unrelated networks - physically located about 700km apart.

Running the VBA script almost works, but the actual conditional format colours do not display - the visible format is still default colours. If I then interactively select any one of those cells (pick one of the 2s, as they're the only ones that should colour), then go Format-Conditional Formatting, I can see the formula and colour that have been assigned by the macro. If I hit OK now, that cell changes colour in accordance with the conditional format. The other 2 is still displaying in default colour, and will continue to until I interactively apply the conditional format to that cell as well (or to all the cells at once).

I played with this a fair bit, as it intrigued me. It seemed to occur whenever I used an array formula for the condition. Using non-array formula behaved as expected. I tried various things like selecting the cells, and activating another sheet then re-activating the first, anything I could think of that might make it redraw. No success. The only way I could get it to properly apply the conditional format was to interactively apply it.

FaV1105

Thanks Crigning Dragon for following up on this. It has intriqued me too but I am not too familiar with how to solve this so have been watching and learning. I shall have to remember that this could be a bug.