Conditional Formatting from VBA

[Andy]

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?

 

[Gord Dibben]

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

 

[Bob Phillips]

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

 

[Andy]

Thank you both - Bob wins on this occasion. I have plugged Bob's code in to
my module and it works fine. Can you explain, for our readers, why my code
was giving spurious results?

Kind Rgds,
Andy.

 

[Gord Dibben]

Thanks Bob

I thought I had tested thoroughly but guess not<g>


Gord

 

[Peter T]

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

 

[Andy]

Thanks Peter - Using the absolute reference to the cell (with the dollars)
did the trick. Thanks all.

 

[Bob Phillips]

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.

 

[Peter T]

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]

Oh good, I have been using that approach for years and I didn't like the
idea that it was up the swannee <bg>.

I hadn't even noticed the other point I admit, I was focussing on getting
the correct cell reference.

 

[Peter T]

Well, just to be irritating <g>, I think that approach would fail if the
activesheet is not same as objSht AND the activecell is not same address as
what was the activecell on objSht, assuming of course an activecell can be
referenced. Make sense?

Regards,
Peter T