Sorry all, I assume the error is basic but I am not sure what I am doing incorrectly.
I am attempting to write a function that takes a cell and converts the characters marked in red to lowercase. I do this by reconstructing the string in a new variable.
This reconstructed string is then returned. However, when I try to use this function in Excel on a string, it returns a #Value error. The code has no compilation errors so I am at a loss. Any help would be appreciated.
Function Convert_Red(rng As Range)
If (rng.Cells.Count > 1) Then
AcceptOneCell = "Only allow 1 cell"
Exit Function
End If
Dim i As Long
Dim text As String
Dim new_text As String
Dim placeholder As String
text = rng.Cells(1, 1).Value
For i = 1 To Len(text)
If rng.Cells(1, 1).Characters(Start:=i, Length:=1).Font.Color vbRed
Then
new_text = new_text + LCase(rng.Cells(1, 1).Characters(Start:=i,
Length:=1))
Else
new_text = new_text + rng.Cells(1, 1).Characters(Start:=i, Length:=1)
End If
i = i + 1
Next
Convert_Red = new_text
End Function
I'm guessing at what your current code is, because the posted code won't compile. I believe you are after the following:
Function Convert_Red(rng As Range)
If (rng.Cells.Count > 1) Then
'Use the correct function name when returning a result
'AcceptOneCell = "Only allow 1 cell"
Convert_Red = "Only allow 1 cell"
Exit Function
End If
Dim i As Long
Dim text As String
Dim new_text As String
Dim placeholder As String
text = rng.Cells(1, 1).Value
For i = 1 To Len(text)
'Fix syntax errors
'If rng.Cells(1, 1).Characters(Start:=i, Length:=1).Font.Color vbRed
'Then
If rng.Cells(1, 1).Characters(Start:=i, Length:=1).Font.Color = vbRed Then
' 1) Fix syntax errors
' 2) Use & for string concatenation
' 3) A Characters object has no default property - specify Text
'new_text = new_text + LCase(rng.Cells(1, 1).Characters(Start:=i,
'Length:=1))
new_text = new_text & LCase(rng.Cells(1, 1).Characters(Start:=i, Length:=1).Text)
Else
' 1) Use & for string concatenation
' 2) A Characters object has no default property - specify Text
'new_text = new_text + rng.Cells(1, 1).Characters(Start:=i, Length:=1)
new_text = new_text & rng.Cells(1, 1).Characters(Start:=i, Length:=1).Text
End If
'Don't mess with the loop counter
'i = i + 1
Next
Convert_Red = new_text
End Function
Note that using Characters will not work if the source range contains a formula, a numeric value, or a date.