Anyone had this problem

[MarkS]

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

 

[Gary Keramidas]

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

 

[Peter T]

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

 

[Bob Phillips]

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