FormatCondition.Modify results in Run-time error '1004' Application-defined or object-defined error

0
votes

I have vba code that sets up a worksheet, One of the features is conditional formatting of column "Y". When I try to change Formula1 of the FormatCondition object, I get the Run-time '1004'.

The Excel Template to be used by Golf Courses to aid in scoring of games played by large groups of players. I use highlighting of cell fill to call attention to significant values - in this case - to the top three players in the "Total Game". Three FormatCondition objects exist in the FormatConditions collection of the range "Y8:Y127". I have written some code that partially enumerates the existing FormatCondition objects, so I know they are there and contain what is expected. I created these through the normal Excel GUI - not vba.

For k = 1 To 3
    WST.Range("$Y$8:$Y$127").FormatConditions(k).Modify xlExpression, , 
      Replace(strScore, "#", k, 1, 1)
Next k

The problem occurs with k = 1.

The Replace function call results in:

=VLOOKUP(A8,$A$7:$AY$127,51,FALSE)=1

I am trying to modify Formula1 of the FormatCondition object which starts out containing : 

=VLOOKUP(A8,$AA$7:$AY$127,25,FALSE)=3

I tried the following from the debugger:

 ? Replace(strScore,"#",k,1,1)
 =VLOOKUP(A8,$A$7:$AY$127,51,FALSE)=1
 ? WST.Range("$Y$8:$Y$127").FormatConditions(k).Formula1
 =VLOOKUP(A8,$AA$7:$AY$127,25,FALSE)=3


x=WST.Range("$Y$8:$Y$127").FormatConditions(k).Modify(xlExpression,,"=VLOOKUP(A8,$A$7:$AY$127,51,FALSE)=1","")

I tried activating the WST sheet but same results.

All tries result in Run-time error '1004'

1
excelvba
Thanks for the formatting update - Perry Sugerman

1 Answers

0
votes

Sorry for bugging folks on this forum. I can get around the problem. The FormatCondition object fails because the worksheet is protected. But the worksheet is protected using:

 `wks.Protect Password:=gPassword, UserInterfaceOnly:=True`

which should allow vba to modify conditional formatting but prevent mods via the user interface. This works for most things in Excel, but clearly not for the FormatCondition object.