Can conditional formatting be used where .....

[Gotroots]

Can conditional formatting be used where text is included with numbers.
Let me explain by example.

A1 = flex 358
A2 = B, 358
A3 = R-U, 354

When A2:A3 numbers do not corresponds to A1 number then highlight cell.
Therefore A3 in this example should be highlighted.
A1:A3 quantities are the result of a formula.

 

[Jacob Skaria]

1. Select the cell/Range (A2:A3). Please note that the cell reference A2
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula

=ISNUMBER(SEARCH(MID(A2,FIND(" ",A2)+1,99),$A$1))=FALSE

OR try the below formula if the above does not work

=ISNUMBER(SEARCH(MID(A2,MATCH(TRUE,ISNUMBER(VALUE(MID(A2,
ROW($1:$255),1))),0),SUM(--(ISNUMBER(VALUE(MID(A2,
ROW($1:$255),1)))))),$A$1))=FALSE


4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

PS: If you are using XL2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format. Enter the formula in the box below.

 

[Ms-Exl-Learner]

Place the Cursor in A1 cell and hit Cntrl+Spacebar now the Total A Column
will be selected and you can notice that the A1 cell will have the White
Background
after selection also.

Now goto Format>>Conditional Formatting>>Formula Is>> Paste the below
formula whichever is required

If you want to set A1 as Stable cell and want to color the cells which is
LESS THAN A1 cell then use the below formula.

=VALUE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT("
",255),LEN(A1)-LEN(SUBSTITUTE(A1,"
",))),255)))<VALUE(TRIM(RIGHT(SUBSTITUTE($A$1," ",REPT("
",255),LEN($A$1)-LEN(SUBSTITUTE($A$1," ",))),255)))

If you want to set A1 as Stable cell and want to color the cells which is
NOT EQUAL to A1 cell value then use the below formula.

=VALUE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT("
",255),LEN(A1)-LEN(SUBSTITUTE(A1,"
",))),255)))<>VALUE(TRIM(RIGHT(SUBSTITUTE($A$1," ",REPT("
",255),LEN($A$1)-LEN(SUBSTITUTE($A$1," ",))),255)))

Remember to Click Yes, if this post helps!