How to change Font Color of Dates without changing color of other text or background using VBA for Excel

I am trying to change the Font color of Dates without changing the color of the other text or background using VBA for Excel but there is a bug in what I have. I am getting the error,

Run-time error '1004': Unable to get the Characters property of the Range class.

As per Google, a "Run-time error '1004': Unable to get the Characters property of the Range class" in Excel VBA means that your code is trying to access the characters within a cell range, but either the range is not selected correctly, is empty, or contains a data type that doesn't support character manipulation, causing an error when trying to use the "Characters" property.

I am trying to change blue colored dates [(RGB(0, 112, 192)] to light blue [RGB(173, 216, 230)]. The dates are in the format d/m, dd/m, d/mm, dd/mm, d/m/yy, dd/m/yy, d/mm/yy, dd/mm/yy and d/m/yyyy, dd/m/yyyy, d/mm/yyyy, dd/mm/yyyy with other text, both before and after the dates. I tried to do that with this VBA code:-

Option Explicit


 
 
Public Sub ChangeBlue()

   Dim DarkBlue As Long
   Dim LightBlue As Long
   Dim Cell As Range
   Dim C As Long
   
   DarkBlue = RGB(0, 112, 192)
   LightBlue = RGB(173, 216, 230)
   
   Application.ScreenUpdating = False
   
   For Each Cell In Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("J:J"))
      'If Not IsEmpty(Cell) And Not Application.WorksheetFunction.IsFormula(Cell)  And InStr(1, Cell, "/") Then ' for Excel 2016 and later
      If Not IsEmpty(Cell) And Left(Cell.Formula, 1) <> "=" And InStr(1, Cell, "/") Then
If Cell.Row Mod 100 = 0 Then Application.StatusBar = Cell.Address
         For C = 1 To Len(Cell.Value)
            If Cell.Characters(Start:=C, Length:=1).Font.Color = DarkBlue Then
               Cell.Characters(Start:=C, Length:=1).Font.Color = LightBlue
            End If
         Next C
      
      End If
   
   Next Cell

   Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

How to Debug the above?

Whatever is in bold is actually blue but not in bold in the original excel sheet and I want to change it to light blue

I am using Microsoft Office 2007, so please keep that in mind. For your information, every date is presently blue or light blue in color

If all you need to do is replace the light blue color with a different one in any cell with a forward slash then this would work:

Sub RecolorText()
   
   Dim c As Range, rngData As Range, v
   
   For Each c In ActiveSheet.UsedRange.EntireRow.Columns("J").Cells
        If Not c.HasFormula Then
            v = c.Value
            If Len(v) > 0 And InStr(v, "/") > 0 Then
                
                c.Value(11) = Replace(c.Value(11), _
                     "Color=""#44B3E1""", "Color=""#FF0000""")
            
            End If
        End If 'has formula
   Next c
End Sub

You'll need to get the "old" and "new" colors by selecting a cell then in the Immediate pane enter ? Selection.Value(11) and check the required color values (see example output below). Above the code is replacing a light blue with red.

For the various arguments you can pass to Value:
https://learn.microsoft.com/en-us/office/vba/api/excel.xlrangevaluedatatype