Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting puzzle
I'm having problems entering conditional formatting via VBA.
I realise that if a condition exists, that I need to modify it so I started by adding 3 conditions to the range. I'm using the following code, but it doesn't work. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("$K$6").Value = "Time" Then With Worksheets(1).Range("$M$8:$N$8").FormatConditions( 1) .Modify xlExpression, , "=AND(K8=0.1,OR(M8<K8*0.9,M8K8*1.1))" .Font.ColorIndex = 3 End With With Worksheets(1).Range("$M$8:$N$8").FormatConditions( 2) .Modify xlExpression, , "=AND(K8=0.1,OR(M8<K8*0.9,M8K8*1.1))" .Font.ColorIndex = 3 End With With Worksheets(1).Range("$M$8:$N$8").FormatConditions( 3) .Modify xlExpression, , "=AND(K8=0.1,OR(M8<K8*0.9,M8K8*1.1))" .Font.ColorIndex = 3 End With End If End Sub There's some very odd stuff happening. The condition formulae are being put in place, but not acting as conditions. This was all written into a new sheet, so there's no legacy formatting/formulae anywhere. If M8 is the active cell, the conditional formatting operates as expected. M8 is red when the active cell is one of the following: K1:K8 M1:M7 If the active cell is anywhere else, the font is black. It doesn't matter what values are placed in K8 & M8. K8:L8 are merged and M8:N8 are merged, but the same thing applies to M8 when all the cells are unmerged. I can't understand what's happening. HELP! -- Ian -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting puzzle
Ian,
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("$K$6").Value = "Time" Then With Worksheets(1).Range("$M$8:$N$8") .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND($K$8=0.1,OR($M$8<$K$8*0.9,$M$8$K $8*1.1))" .FormatConditions(1).Font.ColorIndex = 3 End With End If End Sub When you write a formula to a cell, the references are evaluated as if it were copied from the activecell at the time that you write the formula. So when you had this as your applied formula =AND(K8=0.1,OR(M8<K8*0.9,M8K8*1.1)) if you clicked cell L5, you would get: Formula1:="=AND(L11=0.1,OR(N11<L11*0.9,N11L11*1. 1))" and if you clicked O11, you would get Formula1:="=AND(I5=0.1,OR(K5<I5*0.9,K5I5*1.1))" for cell M8. And you never need the same condition 3 times.... HTH, Bernie MS Excel MVP "IanC" wrote in message ... I'm having problems entering conditional formatting via VBA. I realise that if a condition exists, that I need to modify it so I started by adding 3 conditions to the range. I'm using the following code, but it doesn't work. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("$K$6").Value = "Time" Then With Worksheets(1).Range("$M$8:$N$8").FormatConditions( 1) .Modify xlExpression, , "=AND(K8=0.1,OR(M8<K8*0.9,M8K8*1.1))" .Font.ColorIndex = 3 End With With Worksheets(1).Range("$M$8:$N$8").FormatConditions( 2) .Modify xlExpression, , "=AND(K8=0.1,OR(M8<K8*0.9,M8K8*1.1))" .Font.ColorIndex = 3 End With With Worksheets(1).Range("$M$8:$N$8").FormatConditions( 3) .Modify xlExpression, , "=AND(K8=0.1,OR(M8<K8*0.9,M8K8*1.1))" .Font.ColorIndex = 3 End With End If End Sub There's some very odd stuff happening. The condition formulae are being put in place, but not acting as conditions. This was all written into a new sheet, so there's no legacy formatting/formulae anywhere. If M8 is the active cell, the conditional formatting operates as expected. M8 is red when the active cell is one of the following: K1:K8 M1:M7 If the active cell is anywhere else, the font is black. It doesn't matter what values are placed in K8 & M8. K8:L8 are merged and M8:N8 are merged, but the same thing applies to M8 when all the cells are unmerged. I can't understand what's happening. HELP! -- Ian -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting puzzle
Hi Bernie
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Ian, Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("$K$6").Value = "Time" Then With Worksheets(1).Range("$M$8:$N$8") .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND($K$8=0.1,OR($M$8<$K$8*0.9,$M$8$K $8*1.1))" .FormatConditions(1).Font.ColorIndex = 3 End With End If End Sub Thanks for this, it work a treat. And you never need the same condition 3 times.... Sorry, I was still trying the coding out. I actually have 3 different formulae for different values in K8. I hadn't got round the editing the 2nd & 3rd. -- Ian -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Can you help!!!!! New Puzzle | Excel Discussion (Misc queries) |