selection to color cell if the word promise is in cell

[DKY]

I'm trying to write a selection macro that runs through the selected
cells and checks each cell individually for the word promise. If the
word promise is in the cell I would like the cell to be colored. Heck,
I would like the whole row to be colored but I can't even get the cell
to be colored. Can anyone let me know why this isn't working?

Code:
--------------------
Option Explicit

Sub SELECTION_DOESNT_CONTAIN_PROMISE()

Dim myCell As Range
Dim myRng As Range

For Each myCell In Selection.Cells
Select Case Trim(myCell.Value)
Case Is = "*promised*"
myRng.Interior.ColorIndex = 35
myRng.Interior.Pattern = xlSolid 'Do nothing, keep it
End Select
Next myCell

End Sub

 

[Bob Phillips]

See http://www.contextures.com/xlCondFormat01.html, it is a better technique
than code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Simon Lloyd]

Hi, this worked for me!

Sub promisefind()
Dim rng As Range
Dim mycell
Set rng = Range("A:C")
For Each mycell In rng
If mycell.Value = "Promise" Then
mycell.Select
With Selection
mycell.Interior.ColorIndex = 35
mycell.Interior.Pattern = xlSolid
End With

End If
Next mycell
End Sub

 

[DKY]

The problem with me and conditional formatting is that I'm unsure as to
how to do 'if the cell contains the word'. I can see where you do
equal to and not equal to, but the latter is a difficult one for me to
comprehend.

 

[DKY]

Best I could come up with for conditional formatting:
Formula Is
Code:

 

[RWS]

Try this one, if you start with Option Compare Text, then it doesn't matter
if the word has Capital Letters or not. This will turn the whole row green
for you:

Option Compare Text
Sub LoopRange2()

'Define MyCell variable is a range
Dim MyCell As Range
'Loop using a For Each…Next in selection
For Each MyCell In Selection
If MyCell.Value Like "*promise*" Then
'Set the cell background color to green
MyCell.EntireRow.Interior.ColorIndex = 4

End If
Next

End Sub

 

[Simon Lloyd]

Hi, for your Conditional formatting, which is so much easier!, highligh
all the cells you want have included in the range, click on Format
Conditional Formatting, you need to choose Cell Value Is, then clic
Equal To then in the one remaining box type promise (no inverted coma
needed, no need to type a capital), then choose your format (colour)
click ok, you should now find every cell in your range that contain
the word promise will be your chosen colour!

HTH

Simo