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!

--
Ian
--

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" <(E-Mail Removed)> wrote in message news:ZEozm.14366$(E-Mail Removed)2...
> 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!
>
> --
> Ian
> --
>
>

Hi Bernie

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:(E-Mail Removed)...
> 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
--