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
--------------------


Thanks in advance


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=553243

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)

"DKY" wrote in message
...

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
--------------------


Thanks in advance


--
DKY
------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=553243

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.
Bob Phillips Wrote:
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)

"DKY" wrote in
message
...

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
--------------------


Thanks in advance


--
DKY

------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread:
http://www.excelforum.com/showthread...hreadid=553243



--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=553243

Best I could come up with for conditional formatting:
Formula Is
Code:
--------------------
=SEARCH("promise",$K2)0
--------------------

Then copy the format all the way down the column, that seems to work.
Thanks for the help all.


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=553243

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


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=553243

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
--
RWS


"DKY" wrote:


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
--------------------


Thanks in advance


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=553243

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

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=55324