Conditional Formatting from VBA
 

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?

Conditional Formatting from VBA
 

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?

Conditional Formatting from VBA
 

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?

Conditional Formatting from VBA
 

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.

"Bob Phillips" wrote:

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?

Conditional Formatting from VBA
 

Thanks Bob

I thought I had tested thoroughly but guess not<g


Gord

On Sun, 20 Jul 2008 21:11:38 +0100, "Bob Phillips" wrote:

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

Conditional Formatting from VBA
 

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?

Conditional Formatting from VBA
 

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

"Peter T" wrote:

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?

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?

Conditional Formatting from VBA
 

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?

Conditional Formatting from VBA
 

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.

--
__________________________________
HTH

Bob

"Peter T" <peter_t@discussions wrote in message
...
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?

Conditional Formatting from VBA
 

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


"Bob Phillips" wrote in message
...
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.

--
__________________________________
HTH

Bob

"Peter T" <peter_t@discussions wrote in message
...
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?