Hi Bob,

Actually just one thing with yours, sorry for implying otherwise. I misread
it as being same as Gord's other than one line starting With Range etc

.Interior.ColorIndex = 43

I assume should have been written as -

..FormatConditions(1).Interior.ColorIndex = 43


I can't think why I posted
With Range(.Cells(11, 3), .Cells(11, 3))
iso your
With .Range("C11")

I'm sure I tested with the latter !

Regards,
Peter T



"Bob Phillips" wrote in message
...
What is wrong with mine Peter? You don't have to active the cell or use
absolute references, you just plug the activecell address into the formula
as I did, and Excel adjust to the range being formatted.

--
__________________________________
HTH

Bob

"Peter T" <peter_t@discussions wrote in message
...
I think there are a couple of things wrong with both

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

Unless the acitvecell is C11, in the formula $C$11 should be absolute
(although there is another way if it really needs to be relative). The
other
thing is I assume the colour format should be applied to the
formatcondition.

Regards,
Peter T


"Bob Phillips" wrote in message
...
I think that will do the same Gord.

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


--
__________________________________
HTH

Bob

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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?