Conditional Formatting or VBA ?

[bamaisgreat]

I currently am using this conditional formatting (applies to =$A$4:$A$20)(formula =AND(A4=J4,A4<>"")
Example:
In A4 I have B3335 and J4 I have B3335 so it turns A4 Orange
The issue Im having is some times J4 may have for example B3335-100 so the formatting will not take place.
I need it were if the cell contains the same data as the other it will do the conditional formatting regardless if its exact. Thanks

If this could be fixed in conditional formatting that would be great. Thanks

 

[buxtongt]

I can't test it myself right now, but I would try adding something like:

=*&A4&* and use that to compare J4 to, that way if the text in A4 is contained in J4 at all it will highlight. Hope this helps.

 

[BobUmlas]

Use this formula:
=AND(A4<>"",ISNUMBER(FIND(A4,J4)))

 

[Joe4]

Assuming your are using Excel 2007 or higher, this should work:

=OR(IFERROR(SEARCH(A4,J4,1)>0,FALSE),IFERROR(SEARCH(J4,A4,1)>0,FALSE))

Otherwise, we will need to rewrite the IFERROR logic with IF(ISERROR logic (since IFERROR is a new function in Excel 2007).

 

[bamaisgreat]

worked great, it only fill's A4. Is there away to make it fill both.. =AND(A4<>"",ISNUMBER(FIND(A4,J4)))

 

[buxtongt]

You can add extra cells and/or ranges to the "applies to" part of the conditional formatting. Hope this helps.

 

[bamaisgreat]

so the applies to is between the ""??? Example: =AND(A4<>"J4",ISNUMBER(FIND(A4,J4)))
to fill both A4 and J4

 

[Joe4]

worked great, it only fill's A4. Is there away to make it fill both.. =AND(A4<>"",ISNUMBER(FIND(A4,J4)))

You can apply the same formulas to column J to.
Or, if you lock down the column references, you can use the Format Painter to copy it from one to the other (i.e. $A4, $J4)
Or, highlight both sets of range at once, write the formula (as if applying it to the first row only), and make sure to use the absolute column references.

 

[bamaisgreat]

I cant figure it out, I have the applies to looking like this =$A$4:$A$20,$J$4:$J$20 but it want fill the j4
This is my formula =AND(A4,J4<>"",ISNUMBER(FIND(A4,J4)))

Im confused

 

[gsistek]

You need to use absolute column references as Joe4 pointed out.
Make the formula
=AND($A4,$J4 < > "",ISNUMBER(FIND($A4,$J4)))