PDA

View Full Version : Solved: Find key words in a column and color the row



neditheg
06-14-2012, 03:29 AM
Hello,

I have a 40k rows in an excel file.
In column C, I have names of companies. I want to search this column some key words like "BAR", "Restaurant", "Pizza" ...and if a cell contains the word
bar ---the row or the cell should be colored blue
for restaurant the row or the cell red
and for pizza green.


Thanks!

neditheg
06-14-2012, 04:03 AM
Sub FindMultiKeyword() 'Written by hisham hadi 'http://vbexcel.com Dim Cell As Variant Dim uRange As Range Dim x As String Dim CellTxt As String Dim RngFindTitle As Range Dim FindTitle As Integer Dim tRow As Long Dim tCol As Integer x = "TITLE" Set uRange = ActiveSheet.UsedRange Set RngFindTitle = uRange.Find("Title") If RngFindTitle Is Nothing Then MsgBox ("Sorry! No keyword title is found") Else For Each Cell In uRange CellTxt = UCase(Cells(Cell.Row, Cell.Column).Text) FindTitle = InStr(1, CellTxt, x) If FindTitle <> 0 Then tRow = Cell.Row tCol = Cell.Column MsgBox ("Yes! Title keyword found, Row index = " & _ tRow & ",Column Index = " & tCol) Cells(tRow, tCol).Interior.ColorIndex = 6 End If Next End If End Sub

CodeNinja
06-14-2012, 04:18 AM
conditional formatting should do the trick for you... no need to code anything

see the attached reference:
http://www.contextures.com/xlCondFormat01.html

neditheg
06-14-2012, 04:30 AM
I need to search the keyword "BAR" ..in a company name ...like "Alfredo's BAR" ...or "BAR E Restaurant 2000" ..., "
" conditional formatting can't help me :|

CodeNinja
06-14-2012, 06:22 AM
Neditheg,
Of course conditional formatting can help you... Highlight the entire column you wish to conditionally format, then go to conditional formatting and create a new rule, but use formula... the formula you would want is =find(string,address of first cell in formatted range)...

So, if I wanted to format columns a1 to a100 where all cells that have the word "Bar" in them were red, I would highlight range a1-a100 and create a conditional format rule with the following formula:
=find("Bar",A1)

that would, of course highlight a cell with the word debark in it... if you want to avoid that, I suggest looking for a space before or after...
=or(find(" Bar",A1), find("Bar ",A1).

hope this helps...

neditheg
06-14-2012, 06:24 AM
yap! :) thanks!

shrivallabha
06-14-2012, 06:31 AM
Conditional formatting can be implemented if you want to. But at 40 k rows the sheet might become little slow.

Test this code:
Public Sub ColorRows()
Dim lLastRow As Long
lLastRow = Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To lLastRow
If InStr(UCase(Range("C" & i).Value), "BAR") > 0 Then
Range("C" & i).EntireRow.Interior.Color = vbBlue
ElseIf InStr(UCase(Range("C" & i).Value), "RESTAURANT") > 0 Then
Range("C" & i).EntireRow.Interior.Color = vbRed
ElseIf InStr(UCase(Range("C" & i).Value), "PIZZA") > 0 Then
Range("C" & i).EntireRow.Interior.Color = vbGreen
End If
Next i
End Sub

neditheg
06-14-2012, 06:34 AM
Thanks for the code!
much easier!