Anyone had this problem
 

Hi,
I use this piece of code
Sheets("Control").Range("D5").FormatConditions.Add Type:=xlExpression,
Formula1:= _
"=NOT(OR(D5 = D6, D5 = D7, D5 = D8, D5 = D9, D5 = D10, D5 = D11, D5 =
D12, D5 = D13, D5 = D14))"

And when I look to see what is in the condtitional formatting I get this
=NOT(OR(A65534 = A65535, A65534 = A65536, A65534 = A1, A65534 = A2, A65534 =
A3, A65534 = A4, A65534 = A5, A65534 = A6, A65534 = A7))

I have 10 of these to do sometimes it works and sometimes not

Thanks

Anyone had this problem
 

not sure it's the same thing, or if i can even remember the scenario, but i've
seen similar. i thing it had something to do with the active cell that was
selected on the sheet.

i usually don't use select, but i had to in this case. so, select d5 and then
run your code and then select any other cell before applying the conditional
formatting


--


Gary


"MarkS" wrote in message
...
Hi,
I use this piece of code
Sheets("Control").Range("D5").FormatConditions.Add Type:=xlExpression,
Formula1:= _
"=NOT(OR(D5 = D6, D5 = D7, D5 = D8, D5 = D9, D5 = D10, D5 = D11, D5 =
D12, D5 = D13, D5 = D14))"

And when I look to see what is in the condtitional formatting I get this
=NOT(OR(A65534 = A65535, A65534 = A65536, A65534 = A1, A65534 = A2, A65534 =
A3, A65534 = A4, A65534 = A5, A65534 = A6, A65534 = A7))

I have 10 of these to do sometimes it works and sometimes not

Thanks

Anyone had this problem
 

Looks like the activecell was G12 when you ran your code

Either
Convert the formula to absolute addressing, $D5$ = $D6$ etc
Or
ensure the activecell is D5
Or
if you particularly need your formulas to be "relative" come back for a way
to adjust the formulas before writing to the cf formula so they'll end up as
expected.

Regards,
Peter T

"MarkS" wrote in message
...
Hi,
I use this piece of code
Sheets("Control").Range("D5").FormatConditions.Add Type:=xlExpression,
Formula1:= _
"=NOT(OR(D5 = D6, D5 = D7, D5 = D8, D5 = D9, D5 = D10, D5 = D11, D5 =
D12, D5 = D13, D5 = D14))"

And when I look to see what is in the condtitional formatting I get this
=NOT(OR(A65534 = A65535, A65534 = A65536, A65534 = A1, A65534 = A2, A65534
=
A3, A65534 = A4, A65534 = A5, A65534 = A6, A65534 = A7))

I have 10 of these to do sometimes it works and sometimes not

Thanks

Anyone had this problem
 

It's all to do with Excel adjusting the formula relative to the active cell,
so select D5 first

With Sheets("Control").Range("D5")
.Select
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NOT(OR(D5=D6,D5=D7,D5=D8,D5=D9,D5=D10,D5=D11,D5= D12,D5=D13,D5=D14))"
End With


--
__________________________________
HTH

Bob

"MarkS" wrote in message
...
Hi,
I use this piece of code
Sheets("Control").Range("D5").FormatConditions.Add Type:=xlExpression,
Formula1:= _
"=NOT(OR(D5 = D6, D5 = D7, D5 = D8, D5 = D9, D5 = D10, D5 = D11, D5 =
D12, D5 = D13, D5 = D14))"

And when I look to see what is in the condtitional formatting I get this
=NOT(OR(A65534 = A65535, A65534 = A65536, A65534 = A1, A65534 = A2, A65534
=
A3, A65534 = A4, A65534 = A5, A65534 = A6, A65534 = A7))

I have 10 of these to do sometimes it works and sometimes not

Thanks