Font color start, length

[Thompyt]

How can I get the correct length for the 6-character length of 5 of 5? I am using InStr(1, CharLen, "o") = 3. I can use InStr(1, CharLen, "o") = 2. "of" is 2 characters long so length is 2, I could use 4 to bracket it if needed. I have tried using If then, elseif, end and select case to change the InStr(1, CharLen, "o") to equal 3,4, or 5 and changing the start position other than the true start. if = 3, then it = 2 etc. No change.

Dim WkBk As Workbook, WkSht As Worksheet
Dim RCnt As Integer, CharLen As string, l As Long, si As Long

 .Range("A:D").Rows(RCnt).Font.ColorIndex = 1

For IPCnt = 6 To 255
      ActiveCell = WorksheetFunction.CountA(Sheets(WkSht.Name).Range("A6:A260")) - DrCnt & " of " & WorksheetFunction.CountA(Sheets(WkSht.Name).Range("A6:A260"))
      CharLen = WorksheetFunction.CountA(Sheets(WkSht.Name).Range("A6:A260")) - DrCnt & " of " & WorksheetFunction.CountA(Sheets(WkSht.Name).Range("A6:A260"))
      ActiveSheet.Range("D2:D" & RCnt).Characters(Start:=InStr(1, CharLen, "o"), Length:=2).Font.ColorIndex = 6
 Next IPCnt

Output:
40 of 40
42 of 42
15 of 15
5 of 5
2 of 2
4 of 4


255 of 255
72 of 72

[June7]

Maybe use Split() function. If string will always have 3 parts separated by spaces, consider:
Part1 = Split("5 of 5", " ")(0)
Part2 = Split("5 of 5", " ")(1)
Part3 = Split("5 of 5", " ")(2)

[CJ_London]

misread question

[isladogs]

Maybe I'm also misreading the question, but if you just want the number of characters in e.g. 5 of 5 then use the Len function

?Len("5 of 5")
6

?Len("5 of 25")
7

[Thompyt]

I figured out a work around. I was attempting to use len(string) as well. I do understand the split for the most part but couldn't figure a way to get the part2 to change color in vba, other than maybe posting to a cell, changing the font color, the copy and add between Part 1 and 3 call it part4 and then post to the appropriate cell. then delete the single instance of part2. That would be a lot of coding. I added a space after the "f" on of. That enabled the "of" to be a different color: below the bold color is where I added the extra space the change

For IPCnt = 6 To 255
     ActiveCell = WorksheetFunction.CountA(Sheets(WkSht.Name).Range("A6:A260")) - DrCnt & " of " & WorksheetFunction.CountA(Sheets(WkSht.Name).Range("A6:A260"))
         CharLen = WorksheetFunction.CountA(Sheets(WkSht.Name).Range("A6:A260")) - DrCnt & " of " & WorksheetFunction.CountA(Sheets(WkSht.Name).Range("A6:A260"))
              ActiveSheet.Range("D2:D" & RCnt).Characters(Start:=Len(CharLen) / 2, Length:=2).Font.ColorIndex = 6
Next IPCnt

[isladogs]

Ah. Now I understand why you couldn't just use Len