Finding Value Within a Series (1 Viewer)

DanG

Registered User.
Local time
Today, 05:20
Joined
Nov 4, 2004
Messages
477
Hello,

Below is a partial sample of conditional formatting code I have. Basically in the code I am looking for instances of "69472", but that value may not be the only number in a cell. It may show as: 2205,69472,123.

I can get my code below to work only when 69472 is in the cell by itself, but it won't find instances where is other values in the same cell.

I think it all comes down to how to use the astrisk within the code?

Code:
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=IF(" & MySDA & "=[B]*69472*,1,0)"[/B]
Selection.FormatConditions(2).Interior.ColorIndex = 3

Any help would be appreciated!
 

boblarson

Smeghead
Local time
Today, 05:20
Joined
Jan 12, 2001
Messages
32,059
The expression (formula) for the Condition works differently than for other formulas. What you need to do is create the expression so that it returns a TRUE or a FALSE. If it is True then the condition gets applied. If False it doesn't.

But, in this case, I don't know that conditional formatting will work with partial fields UNLESS the field is a RICH TEXT field. And, even then, I'm not sure that they work for those.
 

DanG

Registered User.
Local time
Today, 05:20
Joined
Nov 4, 2004
Messages
477
Thanks for the reply!

That is what I was afraid of.
 

DanG

Registered User.
Local time
Today, 05:20
Joined
Nov 4, 2004
Messages
477
Ok, base on the thought that a true/false could work, I got the formula COUNTIF($A1,'* 69472 *')>0 to work in the regular conditional formatting interface, but putting it into VBA errors.

Below is the VBA change:
Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=IF(" & MySDA & "=COUNTIF($A1,'* 69472 *')>0"

Any thoughts?
 

boblarson

Smeghead
Local time
Today, 05:20
Joined
Jan 12, 2001
Messages
32,059
Again, no IF should be there. It would be

Formula1:="=COUNTIF($A1,'* 69472 *')>0"
 

NBVC

Only trying to help
Local time
Today, 08:20
Joined
Apr 25, 2008
Messages
317
I think there should be double quotes around the COUNTIF criteria, not single quotes

e.g

="=COUNTIF($A1,""*16785*"")>0"
 

DanG

Registered User.
Local time
Today, 05:20
Joined
Nov 4, 2004
Messages
477
I'm getting there...
I did as you said (twice :)) and I think the only problem now is the use of my variable (MySDA) in the countif formula.

I think it's now a matter of syntax?

Here is what I have now, but am getting errors on running it...
Code:
Formula1:="=COUNTIF(" & MySDA & "," * 69472 * " )>0"
 

boblarson

Smeghead
Local time
Today, 05:20
Joined
Jan 12, 2001
Messages
32,059
Try this:

Code:
Formula1:="=COUNTIF(" & MySDA & "," & Chr(34) & Chr(42) & "69472" & Chr(42) & Chr(34) & ")>0"
 

DanG

Registered User.
Local time
Today, 05:20
Joined
Nov 4, 2004
Messages
477
Soooooo close!

The code you gave me picks up on the 69472 when includded with other values in the cell but not 69472 when in the cell by itself.

Weird though, after the code runs and I check the conditional format interface it looks like it produces the corrext formula to pick up everything I'm looking for (see below)?
=COUNTIF($AY1,"*69472*")>0
 

boblarson

Smeghead
Local time
Today, 05:20
Joined
Jan 12, 2001
Messages
32,059
How about this instead:

Code:
Formula1:="=INSTR(1," & MySDA & "," & Chr(34) & "69472" & Chr(34) & ")>0"
 

DanG

Registered User.
Local time
Today, 05:20
Joined
Nov 4, 2004
Messages
477
It runs with no errors, but does not pick up any instance of 69472 at all.
 

Brianwarnock

Retired
Local time
Today, 12:20
Joined
Jun 2, 2003
Messages
12,701
If MySDA is a named range I think you need

Formula="=COUNTIF(MySDA, ""*69472*"")>0"

Brian
 

Brianwarnock

Retired
Local time
Today, 12:20
Joined
Jun 2, 2003
Messages
12,701
The code you gave me picks up on the 69472 when includded with other values in the cell but not 69472 when in the cell by itself

was the cell preformatted as text?

Brian
 

DanG

Registered User.
Local time
Today, 05:20
Joined
Nov 4, 2004
Messages
477
Sometimes your just have to go with the sloppy solution. I just made 2 conditions/versions out of it and it works fine now.

I thnk in the end your solution was proper, I think there is a problem with the datatype.

Thank you so much!

Here is what I ended up doing:
Code:
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF(" & MySDA & "," & Chr(34) & Chr(42) & "69472" & Chr(42) & Chr(34) & ")>0"
        Selection.FormatConditions(2).Interior.ColorIndex = 15
        
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF(" & MySDA & "," & "69472" & ")>0"
       Selection.FormatConditions(3).Interior.ColorIndex = 15
 

Users who are viewing this thread

Top Bottom