Hi.
I found a VBA sub on another site, but it is giving me an error. The string is from January, so I thought I would post it here for help with the error.
https://superuser.com/questions/1765...d-the-year-202
The error I receive is "Wrong number of arguments or too many property assignments"
The ".Formula1=" in the For Each statement is highlighted when I click OK to the error.
Please Login or Register to view this content.
That is a weird one ... I got it to work by cycling through the long-hand version rather than a declared FormatCondition, like this:
No clue why that works and the other one doesn't but perhaps it's a workaround for you. MMPlease Login or Register to view this content.
MatrixMan.
--------------------------------------
If this - or any - reply helps you, remember to say thanks by clicking on *Add Reputation.
If your issue is now resolved, remember to mark as solved - click Thread Tools at top right of thread.
MatrixMan, thank you for your response.
I still get the same error message and it highlights the entire line beginning ActiveSheet.
I think you need to use the Modify method. Try something like:
Please Login or Register to view this content.
Thank you for your response ByteMarks.
I received the error "Method 'Modify' of object 'FormatCondition' failed" with the following line highlighted.
Please Login or Register to view this content.
Formula1 is read-only so the initial code shouldn't ever work.
ByteMarks code should work, assuming the worksheet isn't protected.
Just as a side note, not every item in the FormatConditions collection is actually a FormatCondition object, but you'd be getting a Type mismatch error if that were the problem.
Rory
Rorya, thank you for your response.
The worksheet is not protected. I'm not sure why I'm getting the error.
Can you post a workbook that demonstrates the problem? You can remove all the data.
Here is a sample spreadsheet. I only added 1 conditional format on cell E27, that changes when E26 is changed to "Some Word". I entered all three code suggestions from this string to a module.
Regarding the MatrixMan suggestion, I tried it exactly as supplied and also tried replacing the < & > with "Some Word" & "Sheet!$C$1" respectively. I wasn't sure what the < & > meant. Either way, I receive the same error message.
The third version works fine here (though it won't do what you expect as the cell reference will be inside quotes).
Tested on Mac and Windows M365.
Last edited by rorya; 09-01-2023 at 09:33 AM.
I got this to work, but the Replace operation seems a bit flaky even in the immediate window.
Please Login or Register to view this content.
Thank you rorya. I still didn't get it to work, but having the word inside quotes does not work for my needs.
ByteMarks, your revision worked for me. Thanks so much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks