XL2BB error when range has conditional formatting

[jmacleary]

I was trying to use XL2BB to answer a thread on here and my selected range contained conditional formatting. When I used the tool I got an error (see pic 2). If I didn't check the conditional formatting box, it generated the data fine.

The generated data (without conditional formatting selected):

Book1
	A	B	C
2	12/12/2019	1	12/01/2020
3	13/02/2019	6	13/08/2019
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=EDATE(A2,B2)

 

[smozgur]

@jmacleary - Thanks for reporting the error.

Could you please let me know the conditional formatting rule in C3?

Thanks again.

 

[jmacleary]

Hello. It is a rule applied to the whole column, as follows:

=AND($C1>0,$C1<TODAY())

and the format applied is a background fill of amber.

 

[jmacleary]

Oh, and I'm using excel 2007 if that makes a difference.

 

[RoryA]

Oh, and I'm using excel 2007 if that makes a difference.

It will if the code in XL2BB uses the DisplayFormat property as that didn't exist in 2007.

 

[Scott T]

It will if the code in XL2BB uses the DisplayFormat property as that didn't exist in 2007.

That does look like the problem as I used CF in 2016 and it works

Book1
	A	B	C
1
2	12/12/2019	1	1/12/2020
3	2/13/2019	6	8/13/2019
Sheet3

Cell Formulas
Range	Formula
C2:C3	C2	=EDATE(A2,B2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C3	Expression	=C2<>""	text	NO

 

[smozgur]

It will if the code in XL2BB uses the DisplayFormat property as that didn't exist in 2007.

Interesting, the add-in supposed to not fail since it is checking Excel version and simply ignoring the displayed format if the function is not supported. However, it looks to be failing somehow.

Added this into the list, and I'll be checking for the upcoming update.

Thanks again.

 

[RoryA]

You could use conditional compilation #If VBA7 then, assuming this is actually the issue (though I’d expect a 438 error rather than 1004).