conditional format

  • Thread starter Thread starter narp
  • Start date Start date
N

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
 
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
 
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).
 
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
 
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
 
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.
 
--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
 
--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
 
That didn't work either. It highlighted the column from the next row down.

Thanks for the help!
 
Sorry no, I didn't understand what it was or how to use it. Do I copy and
paste to VBA to use it?
 
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)
 
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!
 
Send to my address below along with a copy of THIS msg.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
narp said:
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!
Click to expand...
 
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!!!! :)
 
We are glad you got the results desired.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
narp said:
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!!!! :)
Click to expand...
 
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
 
Back
Top