0

How to highlight selected text within excel I followed the last code by Jack BeNimble Is it possible to count the number of rows that were highlighted in addition to this code?

Community
  • 1
  • 1
viji
  • 425
  • 2
  • 6
  • 16

1 Answers1

1

Using code from your link:

Sub Colors()


Dim searchString As String
Dim targetString As String
Dim startPos As Integer
Dim rowcount as long
rowcount = 0
searchString = "abc"
targetString = Cells(2, 1).Value
startPos = InStr(targetString, searchString)

If startPos > 0 Then

    Cells(2, 1).Characters(startPos, Len(searchString)).Font.Color = vbRed
    rowcount = rowcount+1
End If

msgbox "The text was highlighted in " & rowcount & " row(s)!", vbokonly,"Row Count"
End Sub

The above assumes you are only doing one highlight per row which appears to be the case...

Alternatively if you do not want the same row to be counted twice you could build an array of Row numbers and check to ensure the row number being highlighted is not already in the list of rows highlighted previously. This would require extra looping and will slow down performance, especially on larger files.

IAWeir
  • 266
  • 1
  • 6
  • I was looking for the code to highlight those with multiple occurences in a row(the last code byJack BeNimble) and like you said count that row only once.I actually tried `code' : With ActiveSheet .UsedRange.AutoFilter Field:=colToSearch, Criteria1:=fontColor, Operator:=xlFilterFontColor lcount =.AutoFilter.Range.Rows.SpecialCells(xlCellTypeVisible).Count - 3 MsgBox "Total number of rows = " & lastRow - 3 & vbCrLf & fontColor & " Highligted number of rows = " & lcount ' I had given an option of choosing colToSearch and also fontColor(ColorIndex).which didnt work. – viji Aug 06 '15 at 19:06
  • 1
    Did this answer your question. If so, please accept it as the answer. – MatthewD Aug 16 '15 at 00:41