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.
Code:
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