Anyone had this problem

  • Thread starter Thread starter MarkS
  • Start date Start date
M

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