conditional format

[narp]

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

 

[Jacob Skaria]

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

If this post helps click Yes

 

[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).

 

[Jacob Skaria]

Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format

If this post helps click Yes

 

[Don Guillett]

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

 

[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.

 

[Jacob Skaria]

--Select the Column B
--Conditional Formatting>Highlight Cell rules>More rules>
--Selet the last option from the list
'Use a formula to determine which cells to format'

If this post helps click Yes

 

[narp]

I got that far, but dont know how to write the formula. Thanks!

 

[Jacob Skaria]

--In the text box ''Format values where the values are true'..copy and paste
the below
=FIND(D1,B1)

--Click Format Button>Pattern and select your color (say Red)

-- Hit OK

 

[narp]

That didn't work either. It highlighted the column from the next row down.

Thanks for the help!

 

[Don Guillett]

Did you try my macro?

 

[narp]

Sorry no, I didn't understand what it was or how to use it. Do I copy and
paste to VBA to use it?

 

[narp]

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

 

[Don Guillett]

Modified for your data. Try it

Sub highlightmatches()
Dlr = Cells(Rows.Count, "D").End(xlUp).Row
blr = Cells(Rows.Count, "B").End(xlUp).Row
Cells(1, 1).Resize(blr, 3).Interior.ColorIndex = 0
For i = 1 To Dlr
ml = Len(Cells(i, "d"))
For j = 1 To blr
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
(e-mail address removed)

 

[Don Guillett]

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Use the later version I posted

Copy into a regular or sheet module and execute from the sheet where your
data is.
Assign to a button or shape for convenience.

 

[narp]

I opened my spreadsheet (saved as a macro embedded), copied your code to a
VBA screen, saved, and told it to run but it didn't change anything. May I
send you my file and have you look at it to see what I am doing wrong?

Thank you so much for your help!

 

[Don Guillett]

Send to my address below along with a copy of THIS msg.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I opened my spreadsheet (saved as a macro embedded), copied your code to a
VBA screen, saved, and told it to run but it didn't change anything. May
I
send you my file and have you look at it to see what I am doing wrong?

Thank you so much for your help!

 

[narp]

I had refeneced J's in one of my replys and when I change all the j's to DJ's
it works!!! Yeah!!!

Thank you soooo much!!!!

 

[Don Guillett]

We are glad you got the results desired.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I had refeneced J's in one of my replys and when I change all the j's to
DJ's
it works!!! Yeah!!!

Thank you soooo much!!!!

 

[Ashish Mathur]

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