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?

Please Login or Register  to view this content.

Thanks in advance

[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

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

"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> 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]

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.

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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]

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

Please Login or Register  to view this content.

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

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

[Simon Lloyd]

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

HTH

Simon