I found a workaround.
Adding a line of code to select the active sheet works.
Yep, it works fine if "ActiveSheet.Select" is executed.
No responses and nothing found at Microsoft so I presume this is an unknown
issue with Excel 2002 SP3 under Windows XP 2002 SP 1.
Test code
Debug.Print "idx:" & Range("B4").Font.ColorIndex & "
(ThisWorkbook.colors(30) = &H" & Hex(ThisWorkbook.Colors(30)) & " clr:&H" &
Hex(Range("B6").Font.Color)
Debug.Print "idx:" & Range("B5").Font.ColorIndex & "
(ThisWorkbook.colors(31) = &H" & Hex(ThisWorkbook.Colors(31)) & " clr:&H" &
Hex(Range("B4").Font.Color)
Debug.Print "idx:" & Range("B6").Font.ColorIndex & "
(ThisWorkbook.colors(32) = &H" & Hex(ThisWorkbook.Colors(32)) & " clr:&H" &
Hex(Range("B5").Font.Color)
First execution
idx:31 (ThisWorkbook.colors(30) = &H1 clr:&H80
idx:32 (ThisWorkbook.colors(31) = &H0 clr:&H808000
idx:30 (ThisWorkbook.colors(32) = &H2 clr:&HFF0000
Second execution
idx:31 (ThisWorkbook.colors(30) = &H1 clr:&H1
idx:32 (ThisWorkbook.colors(31) = &H0 clr:&H0
idx:30 (ThisWorkbook.colors(32) = &H2 clr:&H2
--
My handle should tell you enough about me. I am not an MVP, expert, guru,
etc. but I do like to help.
"AnExpertNovice" wrote in message
...
This code was executed twice. Both times in the immediate pane to ensure
that nothing in my code was generating this error. Here are the results.
(There should be 3 rows. The first row is the executed code and the next
two rows are the resulting output.)
?"idx:" & Range("B5").font.colorindex & " (ThisWorkbook.colors(32) = &H"
&
Hex(ThisWorkbook.colors(32))& " clr:&H" & Hex(Range("B5").font.color)
idx:32 (ThisWorkbook.colors(32) = &HFF clr:&HFF
idx:32 (ThisWorkbook.colors(32) = &HFF clr:&HFF0000
ColorIndex 32 was set to red and that is what is displayed in Patterns and
Font. Then the font color in cell B5 was set using index 32. (This is
the
bottom right of the palette displaying all 56 colors.)
The first time the code is executed the font color of cell "B5" is
reported
as if it were Blue (&HFF0000). Second, and subsequent, executions
correctly
report the font color as Red (&H0000FF).
This error can be repeated by selecting the worksheet (using Alt-Tab or
any
other method) and then reselecting the VB code window.
Executing this statement reports the same color both times. Thus, the
workaround is not simply referencing the value twice.
..........?Range("B5").font.color & Range("B5").font.color
Is there a workaround or am I "missing" the logic for this problem?
==== Why ColorIndex 32 is being used
============================================
Here is the situation. A worksheet allows the selection of three font
colors and the font color will be used to interpret what the entry means.
So, if the user decides to use Black for two or three of the fonts I need
to
modify the font slightly so they see their chosen color. If the font
colors
did not need to be unique there would be no issue. Luckily, no one is
likely
to detect the difference between 3 cells where the three cells contain
text
with a font color of &H0, &H1, and &H2, yet the program can detect the
difference.
To impact the workbook colors ColorIndex 30, 31, and 32 were chosen to
hold
the three font colors. These indexes are the last three cells on the last
row of the full palette. They are not normally seen when using the drop
down font selection.
When they change the font color on the color selection worksheet the code
checks all three font colors and ensures they are unique. If not, then
one
or two are changed in such a way as to change the font color by 1 unless
all
three were chosen to be either black or white. Then one of the colors
must
be changed by a value of 2.
Once the colors are verified to be unique, their respective ColorIndex is
set to the chosen color, then the cells where the user chooses the font
color are set to match it's respective ColorIndex.
The problem is that the program was changing the colors! I assumed it was
an error until the problem could be replicated using the code shown.
Any suggestions?