Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Conditional Formatting from VBA
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 414
Default 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?

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22,906
Default 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?

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,420
Default 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?



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 414
Default 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?




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22,906
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,600
Default 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?





Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 414
Default 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?






Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,420
Default 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?







Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,600
Default 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?









Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,420
Default 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?











Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,600
Default 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?













Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting Conditional Formatting Icon Sets The Rook[_2_] Excel Discussion (Misc queries) 3 March 7th 09 08:48 PM
Formatting cells in a column with conditional formatting? shamor Excel Discussion (Misc queries) 8 May 19th 08 10:11 PM
Protect Cell Formatting including Conditional Formatting Mick Jennings Excel Discussion (Misc queries) 5 November 13th 07 05:32 PM
conditional Formatting based on cell formatting Totom Excel Worksheet Functions 3 January 20th 07 02:02 PM
Conditional Formatting that will display conditional data BrainFart Excel Worksheet Functions 1 September 13th 05 05:45 PM


All times are GMT +1. The time now is 03:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement