More to do with COMMENTS in Excel 2007

[Guest]

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

 

[Bob Umlas]

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

 

[Guest]

That did the trick. Many thanks

Change this part:
to:
Cells(cell.row,13).value=1

Bob Umlas
Excel MVP