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

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.

Assigning a Variable to an Expression that Includes a Variableand VBA Property
 

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("D1:D10")
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.

---
Per

On 27 Apr., 03:20, bluebird wrote:
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.

Assigning a Variable to an Expression that Includes a Variable and VBA Property
 

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

Sub ToggleColor()
Range("D1:D10").Font.ColorIndex = 7 - Range("D1:D10").Font.ColorIndex
End Sub

--
Rick (MVP - Excel)


"Per Jessen" wrote in message
...
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("D1:D10")
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.

---
Per

On 27 Apr., 03:20, bluebird wrote:
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.

Assigning a Variable to an Expression that Includes a Variable and VBA Property
 

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.