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("D1:D10").Font.ColorIndex = IIf(Range("D1").Font.ColorIndex = 3, 4, 3)
end sub



"bluebird" a écrit dans le message de
...
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 D1:D10.

Sub ChangeColor()
Dim myR as Range, mycolor as Variant
Set myR = Range("D1:D10")
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.