CODEWITHSUNDEEP
vbaexcel
Gerard Ramon
Gerard Ramon

Reputation: 67

Visual Basic excel, How to ask for letter colors

I want to ask for a letter color in an If conditional:

string="asdfghjkl"
for i=1 to len(string)
    letter = mid(string, i, 1)
    input_letter = inputbox("Write a letter")
    if letter = input_letter 'and letter.Font.Color = RGB(31,78,120)
        'my code here
    endif
next

The and letter.Font.Color = RGB(31,78,120) is not working. It says i need an object.

Is there any similar way to ask this? This RGB color is blue, and I am using this code to transform the entire sentence to blue (with the record macro excel setting)

With Selection.Font
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = -0.499984740745262
End With

Thanks

Upvotes: 0

Views: 147

Answers (2)

T.M.
T.M.

Reputation: 9948

The code below comes closest to your OP logic and comment using the .Characters property of a cell Range (B11) containing your string value:

Code

Option Explicit
Sub test()
Dim blue         As Long:   blue = RGB(31, 78, 120)
Dim s            As String: s = "asdfgh"
Dim letter       As String
Dim input_letter As String
Dim i            As Integer
Dim rng          As Range
Set rng = ThisWorkbook.Worksheets("MySheet").Range("B11")
With rng
  .Value = s
  ' color whole string
  .Characters(1, Len(s)).Font.Color = blue
  For i = 1 To Len(s)
    letter = Mid(s, i, 1)
    input_letter = InputBox("Write a letter")
    If letter = input_letter And .Characters(i, 1).Font.Color = blue Then
        'color found character
        .Characters(i, 1).Font.Color = vbWhite
    ElseIf input_letter = "" Then Exit For
    End If
  Next
End With
End Sub

Notes

Always use Option Explicitin your modules declaration head. So you would see that String isn't allowed as variable name as it's a function.

The extra color check in the If condition seems redundant, as characters so long a r e blue.

You seem to prefer repeated InputBoxes within the For - Next loop, could be reduced to a single call.

Upvotes: 0

Matteo NNZ
Matteo NNZ

Reputation: 12665

Regarding your question's problem:

The .Font.Color is a property of the class Range, but in your line of code: 

if letter = input_letter 'and letter.Font.Color = RGB(31,78,120)

... you're trying to access this property in the variable letter, which is a String (you don't explicitly declare it as such, but it gets automatically declared when you execute letter = mid(string, i, 1) just above).

That is why you get an Object required exception: you're trying to access the property .Font.Color on something that is not a Range object (actually, not an Object at all).

Regarding your real need:

I'm not sure to understand what you're trying to do. Are you trying to reach a multi-colored text into a single cell in Excel? If I've got it right, you'll have a string: 

string="asdfghjkl"

(please note: you can't call your variable String, that's a reserved keyword for the code. Think of calling it something else, though I guess you already do that in your real code or you wouldn't be able to execute it at all).

... and, for each letter of that string, 

for i=1 to len(string)

... you want the user to give you a color. In that case, you can't do it in Excel. If not that, could you please express better your real need?

Upvotes: 1

Related Questions