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
--

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
--

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
--