Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to change the colors of a Variable


Hello, Im developing a calender program using macros. One of the things that this calender should do is if an activity of a table is complete and change their font to red. The same thing must happen in the calender which is in other worksheet. My main problem is to covert a variable font. Does any body knows how to do it?

 Cont(0) = Sheet2.Cells(Fnd.Row, 2).Value
        Cont(1) = Sheet2.Cells(2, Fnd.Column).Value
     A = Join(Cont, " ")
     If Sheet2.Cells(Fnd.Row, Fnad.Column).Font.ColorIndex = 3 Then
        A.Characters.Font.ColorIndex = 3
        End If

Here is what I try, A is the string variable that I want to change de color, but compile error continues.



Selected Answer

This should put you on your path to success:-

Private Sub ChangeColor()
    Dim Fnd As Range
    Dim Cont(1)
    Dim A As String
    Set Fnd = Sheet2.Cells(5, 6)
    With Sheet2
'        Cont(0) = .Cells(Fnd.Row, 2).Value
'        Cont(1) = .Cells(2, Fnd.Column).Value
'        A = Join(Cont, " ")
        A = .Cells(Fnd.Row, 2).Value & " " & .Cells(2, Fnd.Column).Value
        ' if you use Option Explicit at the top of your code sheet
        ' VBA will highlight typos like this one ("Fnad")
        ' If .Cells(Fnd.Row, Fnad.Column).Font.ColorIndex = 3 Then
        With .Cells(Fnd.Row, Fnd.Column).Font
            If .ColorIndex <> 3 Then .ColorIndex = 3
        End With
    End With
End Sub


Thanks for your help, Im trying to do the same. But still cant fix the porblem with the string variable. Because you cant call A.Color Index.
If I change the variable type could be done?
Jonacz (rep: 4) Sep 5, '17 at 10:42 am
No, you don't call A.ColorIndex. You set the color index for the font of the cell. Please look at the code above. This is the4 code which sets the color:-
If .ColorIndex <> 3 Then .ColorIndex = 3
Variatus (rep: 4879) Sep 5, '17 at 11:00 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login