Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
evaluation a variable in an expression | Excel Programming | |||
Assigning a row to a variable | Excel Programming | |||
How to specify a Range which includes an integer variable | Excel Programming | |||
Scope of variable includes all Form _and_ Code modules?? | Excel Programming | |||
variable not equal to expression | Excel Programming |