More to do with COMMENTS in Excel 2007

Dim i As Integer
Dim myText As String, List, myTotal As Double
Dim cell As Range
Set cmt = ActiveSheet.Comments
Set rng1 = Range("target")
Selection.SpecialCells(xlCellTypeComments).Select

For Each cell In Selection
cell.Select
myTotal = 0

myText = UCase(cell.Comment.Text)
If InStr(myText, rng1) Then
cc = col
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 39372
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If


Next cell
End Sub
The code above reads all the COMMENTS in columns A to I looking for a given
word. When that target word is found it colors the cell brown.
I would much prefer to NOT COLOR the cell but to place a 1(one) in a column
M on the same line as where the target word was found.
I would appreciate any help\Thanks

Change this part:
> With Selection.Interior
> .Pattern = xlSolid
> .PatternColorIndex = xlAutomatic
> .Color = 39372
> .TintAndShade = 0
> .PatternTintAndShade = 0
> End With
to:
Cells(cell.row,13).value=1

Bob Umlas
Excel MVP


"pcor" <pcor RemoveThis @discussions.microsoft.com> wrote in message
news:E785E27F-1851-472D-BB1A-411BEDAC2634@microsoft.com...
> Dim i As Integer
> Dim myText As String, List, myTotal As Double
> Dim cell As Range
> Set cmt = ActiveSheet.Comments
> Set rng1 = Range("target")
> Selection.SpecialCells(xlCellTypeComments).Select
>
> For Each cell In Selection
> cell.Select
> myTotal = 0
>
> myText = UCase(cell.Comment.Text)
> If InStr(myText, rng1) Then
> cc = col
> With Selection.Interior
> .Pattern = xlSolid
> .PatternColorIndex = xlAutomatic
> .Color = 39372
> .TintAndShade = 0
> .PatternTintAndShade = 0
> End With
> End If
>
>
> Next cell
> End Sub
> The code above reads all the COMMENTS in columns A to I looking for a
> given
> word. When that target word is found it colors the cell brown.
> I would much prefer to NOT COLOR the cell but to place a 1(one) in a
> column
> M on the same line as where the target word was found.
> I would appreciate any help\Thanks