FormatConditions.Add - Invalid Procedure Call when trying to add IsNumber(Search))

[Clear]

Hey,

I want to ask what is wrong with this code? When I'm trying to add it, it return an Error "Invalid Procedure call or argument". I can't figure why.

The formula is working well in Excel if added manually :/

Please Login or Register  to view this content.

The code above resolves to:

Please Login or Register  to view this content.

The formula works great if put on cell (even by vba) and also works if put manually on conditional formatting. So why doesn't it work when used by vba?


I tried it also on empty workbook so it's not workbook-specified.

[AB33]

Change the dim from

Dim cf

to
Dim cf as range

[Clear]

Change the dim from

Dim cf

to
Dim cf as range

Unfortunately, it doesn't change anything. I didn't add it because to be exact it returns a FormatConditions object. Changing type to others also doesn't work.
The code works when I use other formulas, just this one formula seems to be bugging Excel and I can't get why.
It's just a fragment of code I copied to empty workbook to make sure, it's a part that makes problems.

[AB33]

It is because you are on active cell, but when you run your cursor might not be on active cell.
Remove active cell and replace it by proper referencing a cell or range, or make sure that the cursor on the active cell when you run the code.

[Clear]

Using ActiveCell was just to simplifying now. It seems it's formula that excel hates that's why I simplified everything other to focus on Formula.

I changed a code to clarification.

Please Login or Register  to view this content.

[AB33]

The last attachment fails—can't download it.

[Clear]

I reuploaded it on this post.

[romperstomper]

Since you are in Poland, I'd assume you use semicolons not commas as list separators, so you need:

Please Login or Register  to view this content.

[AB33]

I have recoded a macro to test it as I can't remember the syntax of CF. I did not get or see any error. I presume you are missing the colouring bit.

Please Login or Register  to view this content.

[Clear]

Since you are in Poland, I'd assume you use semicolons not commas as list separators, so you need:

Please Login or Register  to view this content.

Bro, I would hug you if I could. That was the case. It has to be entered exactly as if I would enter it manually, so semicolons and I would have to translate formulas.
That's complete bullshit. If it wasn't for you, I would never expect that.

[romperstomper]

It is a very stupid and inconsistent thing in Excel, sadly.