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 |
More to do with COMMENTS in Excel 2007
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" wrote in message ... 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 |
More to do with COMMENTS in Excel 2007
That did the trick. Many thanks "Bob Umlas" wrote: 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" wrote in message ... 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 |
All times are GMT +1. The time now is 07:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com