Testing CF Conditions

[Constantly Amazed]

Hi

In a previous question on testing CF conditions I was directed to a website
xldynamic.com/source/xld.cfconditions.html. I need to use the function
listed as IsCFMet but it does not giving me the expected response when using
a formula condition.

As a check I created a condition cell (value is, equal to, 1, pattern red)
in A1 and in B1 used the function. It returned TRUE as expected. However,
the real formulas are more complex and when I entered an example in A2 of
Formula is, =LEN(A2)>40, pattern red OR cell value is, equal to,="", pattern
blue the function returns FALSE when the cell is empty (instead of TRUE). If
I enter more than 40 characters to activate the other condition the function
returns TRUE (which is correct).

Is there something wrong with my conditional format or something which needs
changing in the code? I would be grateful for any help on this.

Thanks

G

 

[merjet]

I stepped through the code and when it got to the line:
IsCFMet = rng.Value = oFC.Formula1
rng.Value = Empty, not the same as =""

I changed your 2nd condition to Formula Is: = ISBLANK(A2) = TRUE
and IsCFMet() returned TRUE.

Hth,
Merjet

 

[Constantly Amazed]

Thanks Merjet but there is still a problem with another condition. This time
as a second condition of I have cell value, is not equal to," "(to represent
a space), green. When I enter a space so that neither condition is met
IsCFMet continues to return a value of TRUE instead of FALSE. I can
understand empty not being the same as "" but not this condition where it is
specifically a space.

Thanks

 

[merjet]

I got IsCFMet to return True by testing for
Formula Is = CODE(32). However, I think you
did find a bug -- testing for any text such as
"xxx". The following is a way to fix the bug.

Dim vX as Variant

If oFC.Type = xlCellValue Then
vX = rng.Value
If Application.IsText(vX) Then _
vX = "=" & Chr(4) & vX & Chr(4)
Select Case oFC.Operator
Case xlEqual
IsCFMet = vX = oFC.Formula1
Case xlNotEqual
IsCFMet = vX <> oFC.Formula1
Case xlGreater
IsCFMet = vX > oFC.Formula1
Case xlGreaterEqual
IsCFMet = vX >= oFC.Formula1
Case xlLess
IsCFMet = vX < oFC.Formula1
Case xlLessEqual
IsCFMet = vX <= oFC.Formula1
IsCFMet = (vX >= oFC.Formula1 And _
vX <= oFC.Formula2)
Case xlNotBetween
IsCFMet = (vX < oFC.Formula1 Or _
vX > oFC.Formula2)
End Select
Else

Hth,
Merjet

 

[Constantly Amazed]

Hi Merjet

Thanks. I used the Code(32) route but when I tried the amended code the
result would not change from TRUE when the field was blank (and a condition
activate) to FALSE when I entered a character (and no condition active). If
it is possible to correct this it could be very useful in case I need to test
for specific text strings on another project although the use of Char(32) is
OK in this instance.

G