Conditional Format, Text not Cell

[IZ2018]

I would like to apply a Conditional Format to TEXT in CELL not to CELL, see attachment.
If it is too complicated then I want to apply conditional format to cell.

 

[Hui]

What you want cannot be done use Conditional Formatting directly
Conditional Formatting only applies to cells not parts of cells

What you want could be applied using VBA

 

[IZ2018]

Ok, but the second option, could be done, no ?
Meaning, if there in string is only one not Recognized, then RED

 

[IZ2018]

cell red, not text (for second option)

 

[Hui]

If a VBA solution is used, you can use what ever criteria you require

I will not have time to tackle this for a few days, so I hope somebody else picks it up

 

[bosco_yip]

The second option,

1] Define Name >>

>> Name : MatchNumber

>> Refer to :
=SUMPRODUCT(ISNUMBER(MATCH(Sheet1!$A$3:$A$14&"",TRIM(MID(SUBSTITUTE(Sheet1!$D12,",",REPT(" ",30)),{1,30,60,90,120},30)),0))*(Sheet1!$B$3:$B$14="X"))

2] Select D12:D18 >> Conditional Formatting >> New rule >> Use a formula >> Rule formula :

=MatchNumber<>(LEN($D12)-LEN(SUBSTITUTE($D12,",",""))+1)

>> Format >> Cell fill color >> red >> OK

Regards
Bosco

 

[IZ2018]

I have one more question here :
In Column D12:D18 (where the formating is), if I delete all (so column is clean), cells are still formatted RED so I believe there should be something like, if cells are empty then nothing.

 

[bosco_yip]

I have one more question here :
In Column D12:D18 (where the formating is), if I delete all (so column is clean), cells are still formatted RED so I believe there should be something like, if cells are empty then nothing.

In range D12:D18, after delete all cells content, you also need to clear the Conditional Formatting Rules.



Regards

 

[IZ2018]

I do not want to clear rules !
Formatting should apply if there is data in D.

 

[Hui]

you will need to add another CF to cater for that

 

[NARAYANK991]

Hi ,

Just change Bosco's CF formula to :

=AND($D12 <> "", MatchNumber<>(LEN($D12)-LEN(SUBSTITUTE($D12,",",""))+1))

Now , when you clear your range D12:D18 , the CF rule will not color it red.

Narayan

 

[IZ2018]

Yes, thanks a lot

 

[IZ2018]

Thanks for all responses.
I have attached another file regarding formatting
This time I have simplified the requirements, there is a single Criteria (not multiple like 1,2,43.
I wonder if the formula is much simple now

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[IZ2018]

Yes, one more info please. How would you change Formula :

=IF(ISERROR(MATCH($E2, $A$2:$A$9, 0)), 1, IF(INDEX($B$2:$B$9, MATCH($E2, $A$2:$A$9, 0)) <> "X", 1,))

knowing that in E2:E9, so in $E2, I have not Numbers but Extracted Numbers from Text so my numbers are Text, and formula is not working in this case
I am trying to replace $E2 with VALUE($E2) but still not ok

 

[NARAYANK991]

Hi ,

Doesn't this work ?

=IF($E2 = "", 0, IF(ISERROR(MATCH($E2+0, $A$2:$A$9, 0)), 1, IF(INDEX($B$2:$B$9, MATCH($E2+0, $A$2:$A$9, 0)) <> "X", 1,)))

Narayan

 

[IZ2018]

So far no, it work for me only if I convert to numbers at extraction, so in $E2 I have numbers. But text number and using the formula , no

 

[NARAYANK991]

So far no, it work for me only if I convert to numbers at extraction, so in $E2 I have numbers. But text number and using the formula , no

Hi ,

Sorry , but it works at my end.

Unless you upload your workbook with the data in it , I am afraid I cannot help.

Narayan