Conditional formatting puzzle

[IanC]

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,M8>K8*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,M8>K8*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,M8>K8*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!

 

[Bernie Deitrick]

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,M8>K8*1.1))

if you clicked cell L5, you would get:

Formula1:="=AND(L11>=0.1,OR(N11<L11*0.9,N11>L11*1.1))"

and if you clicked O11, you would get

Formula1:="=AND(I5>=0.1,OR(K5<I5*0.9,K5>I5*1.1))"

for cell M8.

And you never need the same condition 3 times....


HTH,
Bernie
MS Excel MVP

 

[IanC]

Hi Bernie

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.