Conditional Formatting from VBA
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?
|