Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting a chart from Excel 2007 to Word 2007 trouble | Charts and Charting in Excel | |||
in excel useing comments how do you add clip art to comments? | New Users to Excel | |||
Exporting Quickbooks 2007 to Excel 2007 | Excel Worksheet Functions | |||
Problems with my links to an excel 2007 file from a word 2007 file | Links and Linking in Excel | |||
comments in excel | Excel Worksheet Functions |