Dim objSht
Set objSht = ActiveSheet
With objSht
With Range(.Cells(11, 3), .Cells(11, 3))
..FormatConditions.Delete
..FormatConditions.Add Type:=xlExpression, _
Formula1:="=ISNUMBER(SEARCH(""KC"",C11))"
..Interior.ColorIndex = 43
End With
End With


Gord Dibben MS Excel MVP

On Sun, 20 Jul 2008 11:46:00 -0700, Andy wrote:

I'm trying to set the Conditional formatting of a cell in Excel from Access -
Don't ask !

The condition? I the characters 'KC' appear anywhere in C11, I need to set
color to green.

Anyway - the code :-

Imagine that objSht is a Worksheet object that I am workingh on from Access
...

With objSht
.Range(.Cells(11, 3), .Cells(11, 3)).FormatConditions.Delete
.Range(.Cells(11, 3), .Cells(11, 3)).FormatConditions.Add
Type:=xlExpression, _ Formula1:="=ISNUMBER(SEARCH(""KC"",C11))"
.Range(.Cells(11, 3), .Cells(11,
3)).FormatConditions(1).Interior.ColorIndex = 43
End With

Compiles and runs fine but Condition does not work and when I examine the
Conditional formatting expression in Excel the 'C11' in the expression has
been changed to 'IU17' every time ??

Can anyone please help?