Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |