Highlight Cells That Contain Specific Text or Keyword

[Howardc]

I have a spreadsheet, where I need sort, extract or highlight the text where the word Cash appears in Col H

If the data containing the text "Cash" cannot be sorted together, then I would like a macro to extract the data based on this criteria. The extracted data must be extracted to a seperate sheet containing Cols B to H. The other alternative is to highlight the data in Col H where cash appears in Col H

Your assistance in resolving this is most appreciated

Sortting using Custom

[patel]

Sub a()
LR = Cells(Rows.Count, "H").End(xlUp).Row
For j = 1 To LR
  If InStr(Cells(j, "H").Text, "Cash") > 0 Then
    Cells(j, "H").Interior.ColorIndex = 6
  End If
Next
End Sub

[ramananhrm]

Please try this as well.

Navigate to Conditional formatting > New rule > Use a formula to determine which cells to format > Paste this function & highlight with your desired color.
=ISNUMBER(SEARCH("Cash",H2))

[Howardc]

Hi Guys

Thanks for the help, much appredciated

[Howardc]

Hi Patel

Thanks for the help, much appreciated.

Your code works perfvectly on the uploaded file. Hower, when trying to run it on another workbook, it did not work-see attached sample

Kindly check and amend your code accordingly

Regards

Howard

[patel]

Option Compare Text

Sub a()
LR = Cells(Rows.Count, "H").End(xlUp).Row
For j = 1 To LR
  If InStr(Cells(j, "H").Text, "Cash") > 0 Then
    Cells(j, "H").Interior.ColorIndex = 6
  End If
Next
End Sub

[Howardc]

Hi Patel

Thanks for the help. Macro works perfectly

Regards

Howard

[Howardc]

Hi Patel

It would be appreciated if you could amend your code to highlight the text cash where the value is 25000 and over. The values are in Col G. I have tried to amend but cannot get it to work


Option Compare Text

Sub a()
LR = Cells(Rows.Count, "H").End(xlUp).Row
For j = 1 To LR
If InStr(Cells(j, "H").Text, "Cash") > 0 And Cells(j, "G").Value >= 25000 Then
Cells(j, "H").Interior.ColorIndex = 6
End If
Next
End Sub

[patel]

your change seems correct, it works on your attached file

[Howardc]

Hi Patel

My apologies. The additional code that I added i working. I forot to clear the highliighted items before running the code

I have now added cose to first clear the highlighted items


Regards

Howard