How can I create a function that will highlight a cell in Column B if the
cell info contains the info in Column D? Info in Column D is not always
aligned with info in Column B.

A B C D
06/17/09 OR123 1.00 123
07/01/09 EC458 2.00 528
07/01/09 OR528 15.00 924
--
narp

1. Select the Column B
2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=FIND(D1,B1)
4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

If this post helps click Yes
---------------
Jacob Skaria


"narp" wrote:

How can I create a function that will highlight a cell in Column B if the
cell info contains the info in Column D? Info in Column D is not always
aligned with info in Column B.

A B C D
06/17/09 OR123 1.00 123
07/01/09 EC458 2.00 528
07/01/09 OR528 15.00 924
--
narp

I forgot to say I am using Excel 2007. Where do I find the conditional
format that allows for multiple conditions. Also to clarify, I want to look
for info in cell J2 within the range of column b (b1.b200).
--
narp


"Jacob Skaria" wrote:

1. Select the Column B
2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=FIND(D1,B1)
4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

If this post helps click Yes
---------------
Jacob Skaria


"narp" wrote:

How can I create a function that will highlight a cell in Column B if the
cell info contains the info in Column D? Info in Column D is not always
aligned with info in Column B.

A B C D
06/17/09 OR123 1.00 123
07/01/09 EC458 2.00 528
07/01/09 OR528 15.00 924
--
narp

Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a
formula to determine which cells to format

If this post helps click Yes
---------------
Jacob Skaria


"narp" wrote:

I forgot to say I am using Excel 2007. Where do I find the conditional
format that allows for multiple conditions. Also to clarify, I want to look
for info in cell J2 within the range of column b (b1.b200).
--
narp


"Jacob Skaria" wrote:

1. Select the Column B
2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=FIND(D1,B1)
4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

If this post helps click Yes
---------------
Jacob Skaria


"narp" wrote:

How can I create a function that will highlight a cell in Column B if the
cell info contains the info in Column D? Info in Column D is not always
aligned with info in Column B.

A B C D
06/17/09 OR123 1.00 123
07/01/09 EC458 2.00 528
07/01/09 OR528 15.00 924
--
narp

I'm not getting it to work.

Here is my data sample:
Date Number c Processing
06/14/09 EC17251 34002
07/20/09 EC17570 17251
08/07/09 EC17716
07/15/09 OR33889
07/16/09 OR33956
07/17/09 OR34002
07/22/09 OR34178

I went to the New Formatting Rule as directed but don't know what to enter
in the "Format values where this formula is true:" box. I want it to lookup
info in column D and if it is found in column B then highlight that cell.
For example, Does a cell in Column B contain the info that is found in any
cell in column D--otherwise written as: does 34002 (cell D2) show in any cell
in column B (cell B7) and if so highlight cell B7.

Sorry for the confusion. Thank you for your assistance.

--
narp


"Jacob Skaria" wrote:

Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a
formula to determine which cells to format

If this post helps click Yes
---------------
Jacob Skaria


"narp" wrote:

I forgot to say I am using Excel 2007. Where do I find the conditional
format that allows for multiple conditions. Also to clarify, I want to look
for info in cell J2 within the range of column b (b1.b200).
--
narp


"Jacob Skaria" wrote:

1. Select the Column B
2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=FIND(D1,B1)
4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

If this post helps click Yes
---------------
Jacob Skaria


"narp" wrote:

How can I create a function that will highlight a cell in Column B if the
cell info contains the info in Column D? Info in Column D is not always
aligned with info in Column B.

A B C D
06/17/09 OR123 1.00 123
07/01/09 EC458 2.00 528
07/01/09 OR528 15.00 924
--
narp

--Select the Column B
--Conditional FormattingHighlight Cell rulesMore rules
--Selet the last option from the list
'Use a formula to determine which cells to format'

If this post helps click Yes
---------------
Jacob Skaria


"narp" wrote:

I'm not getting it to work.

Here is my data sample:
Date Number c Processing
06/14/09 EC17251 34002
07/20/09 EC17570 17251
08/07/09 EC17716
07/15/09 OR33889
07/16/09 OR33956
07/17/09 OR34002
07/22/09 OR34178

I went to the New Formatting Rule as directed but don't know what to enter
in the "Format values where this formula is true:" box. I want it to lookup
info in column D and if it is found in column B then highlight that cell.
For example, Does a cell in Column B contain the info that is found in any
cell in column D--otherwise written as: does 34002 (cell D2) show in any cell
in column B (cell B7) and if so highlight cell B7.

Sorry for the confusion. Thank you for your assistance.

--
narp


"Jacob Skaria" wrote:

Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a
formula to determine which cells to format

If this post helps click Yes
---------------
Jacob Skaria


"narp" wrote:

I forgot to say I am using Excel 2007. Where do I find the conditional
format that allows for multiple conditions. Also to clarify, I want to look
for info in cell J2 within the range of column b (b1.b200).
--
narp


"Jacob Skaria" wrote:

1. Select the Column B
2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=FIND(D1,B1)
4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

If this post helps click Yes
---------------
Jacob Skaria


"narp" wrote:

How can I create a function that will highlight a cell in Column B if the
cell info contains the info in Column D? Info in Column D is not always
aligned with info in Column B.

A B C D
06/17/09 OR123 1.00 123
07/01/09 EC458 2.00 528
07/01/09 OR528 15.00 924
--
narp

This may be more useful than CF to uncolor the range and then color for
matches on the right.

Sub highlightmatches()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Cells(1, 1).Resize(lr, 3).Interior.ColorIndex = 0
For i = 1 To lr
ml = Len(Cells(i, "d"))
For j = 1 To lr
If Right(Cells(j, "b"), ml) = CStr(Cells(i, "d")) Then
Cells(j, "b").Interior.ColorIndex = 6
End If
Next j
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"narp" wrote in message
...
How can I create a function that will highlight a cell in Column B if the
cell info contains the info in Column D? Info in Column D is not always
aligned with info in Column B.

A B C D
06/17/09 OR123 1.00 123
07/01/09 EC458 2.00 528
07/01/09 OR528 15.00 924
--
narp

I copied and pasted the macro in excel but it highlight anything.
--
narp


"Don Guillett" wrote:

This may be more useful than CF to uncolor the range and then color for
matches on the right.

Sub highlightmatches()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Cells(1, 1).Resize(lr, 3).Interior.ColorIndex = 0
For i = 1 To lr
ml = Len(Cells(i, "d"))
For j = 1 To lr
If Right(Cells(j, "b"), ml) = CStr(Cells(i, "d")) Then
Cells(j, "b").Interior.ColorIndex = 6
End If
Next j
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"narp" wrote in message
...
How can I create a function that will highlight a cell in Column B if the
cell info contains the info in Column D? Info in Column D is not always
aligned with info in Column B.

A B C D
06/17/09 OR123 1.00 123
07/01/09 EC458 2.00 528
07/01/09 OR528 15.00 924
--
narp

Hi,

You may use this formula in conditional formatting
=ISNUMBER(LOOKUP(2,1/SEARCH(D20,$B$20:$B$22))). Apply a format of your
choice and thencopy paste the conditional formatting down.

Do let me know how this works

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"narp" wrote in message
...
How can I create a function that will highlight a cell in Column B if the
cell info contains the info in Column D? Info in Column D is not always
aligned with info in Column B.

A B C D
06/17/09 OR123 1.00 123
07/01/09 EC458 2.00 528
07/01/09 OR528 15.00 924
--
narp

Assuming that A2:D4 contains the data, try...

1) Select B2

2) Format Conditional Formatting Formula Is

If the relevant information for Column B is always the last 3 characters:

=MATCH(RIGHT($B2,3)+0,$D$2:$D$4,0)

Otherwise:

=LOOKUP(9.99999999999999E+307,FIND($D$2:$D$4,$B2))

3) Choose 'Format'

4) Select the desired formatting

5) Click Ok twice

6) Copy the formatting to the other cells...

Copy Paste Special Formats

or

Format Painter

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
narp wrote:

How can I create a function that will highlight a cell in Column B if the
cell info contains the info in Column D? Info in Column D is not always
aligned with info in Column B.

A B C D
06/17/09 OR123 1.00 123
07/01/09 EC458 2.00 528
07/01/09 OR528 15.00 924