Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Find string and color cell if found

Posted on 2013-01-19
Medium Priority
702 Views
Hi,

I have the following code which looks for cells in column B and colors any cell blue that contains a certain string. This works ok but now I need to color any cell that has WATERBALL in it, even if WATERBALL is only a partial match (like "Red  WATERBALL", or "Blue Waterball"). This code only works for an exact match including case.

lRow = Range("B" & Rows.Count).End(xlUp).Row
Set MR = Range("B2:B" & lRow)
For Each cell In MR
If cell.Value = "WATERBALL" Then cell.Interior.ColorIndex = 28
Next

Further, I need a second string "Float" found and colored some other color (purple?)
This works but again, only for an exact match.

lRow = Range("B" & Rows.Count).End(xlUp).Row
Set MR = Range("B2:B" & lRow)
For Each cell In MR
If cell.Value = "WATERBALL" Then cell.Interior.ColorIndex = 28
If cell.Value = "Float" Then cell.Interior.ColorIndex = 39
Next

swjtx99
0
Question by:swjtx99
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 2

Author Comment

ID: 38797647
Hmm....Googling led me to this which didn't work at all....

Sub Find_String()

lRow = Range("B" & Rows.Count).End(xlUp).Row
Set MR = Range("B2:B" & lRow)
For Each cell In MR
If UCase(ActiveCell.Value) Like "WATERBALL" Then cell.Interior.ColorIndex = 28
If UCase(ActiveCell.Value) Like "Float" Then cell.Interior.ColorIndex = 39
Next

End Sub
0

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 38797651
Hello,

maybe

``````Option Explicit

Sub test()

Dim lrow As Long
Dim MR As Range
Dim cel As Range

lrow = Range("B" & Rows.Count).End(xlUp).Row
Set MR = Range("B2:B" & lrow)
For Each cel In MR
If InStr(UCase(cel.Value), "FLOAT") Then cel.Interior.ColorIndex = 39
If InStr(UCase(cel.Value), "WATERBALL") Then cel.Interior.ColorIndex = 28

Next

End Sub
``````

cheers, teylyn
0

Author Closing Comment

ID: 38797666
Works!

Thanks for your help,

swjtx99
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month7 days, 21 hours left to enroll

#### 715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.