Assigning a Variable to an Expression that Includes a Variable andVBA Property

[bluebird]

Consider this macro, which is supposed to toggle font color between
red (colorindex value = 3) and green (colorindex value =4). Put
whatever values you like in range D110.

Sub ChangeColor()
Dim myR as Range, mycolor as Variant
Set myR = Range("D110")
mycolor = myR.Font.ColorIndex
If mycolor = 3 Then
mycolor = 4
ElseIf mycolor = 4 Then
mycolor = 3
End If
End Sub

The macro fails to change the font color on the screen, but amazingly
the macro does change
color value, mycolor, from 3 to 4 and vice versa in the Immediate
Window. If you revise the macro
by substituting myR.Font.ColorIndex for the variable mycolor, the
macro works fine. Is there a problem with assigning a variable to a
VBA expression including properties like Font.ColorIndex?

Has anyone ever found a reference that explains quirky situations like
this one?

Thank you.

 

[Per Jessen]

Hi

You have to test and set the color cell by cell, and you are only
changing the color code held in the variable myColor

Sub ChangeColor()
Dim myR As Range, mycolor As Variant
Set myR = Range("D110")
For Each cell In myR.Cells
mycolor = cell.Font.ColorIndex
If mycolor = 3 Then
cell.Font.ColorIndex = 4
ElseIf mycolor = 4 Then
cell.Font.ColorIndex = 3
End If
Next
End Sub

Hopes this helps.

 

[Rick Rothstein]

If the cells are already one color or the other, then you can toggle them
without a loop...

Sub ToggleColor()
Range("D110").Font.ColorIndex = 7 - Range("D110").Font.ColorIndex
End Sub

--
Rick (MVP - Excel)


Hi

You have to test and set the color cell by cell, and you are only
changing the color code held in the variable myColor

Sub ChangeColor()
Dim myR As Range, mycolor As Variant
Set myR = Range("D110")
For Each cell In myR.Cells
mycolor = cell.Font.ColorIndex
If mycolor = 3 Then
cell.Font.ColorIndex = 4
ElseIf mycolor = 4 Then
cell.Font.ColorIndex = 3
End If
Next
End Sub

Hopes this helps.

 

[Charabeuh]

hi,
mycolor is a variable and does not refer to myR.Font.index.
Insert the line "myR.Font.index=mycolor" before the "end sub"

you could also write

sub ChangeColor()
Range("D110").Font.ColorIndex = IIf(Range("D1").Font.ColorIndex = 3, 4, 3)
end sub