selection.font.color returns wrong color; the first execution

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?

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" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
news:[email protected]...
> 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?
>
>

A guess - are you confusing the Activeworkbook and ThisWorkbook's palette's
(based on the observation your debug code returns info about each).

If not post the full code.

Regards,
Peter T

"AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
news:[email protected]...
> 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?
>
>

No, but good point.

In this case the code resides in the current workbook so ActiveWorkbook and
ThisWorkbook are the same.

Thanks for the thought.

A co-worker said he had a similar situation with Excel workbooks created by
Business Objects. Opening the workbook allowed viewing the report but
trying to print or do a print preview generated an error. Clicking on the
worksheet tab "fixed" the problem. Essentially they are maunally doing an
Activeworksheet.Select to work around the issue.

So what's the actual code you use.

#32 in a default palette is 100% blue.

It is possible to create a workbook that sustains two unique palettes
concurrently, one default and one customized, each viewable in different
windows of the same workbook. There's a bit of a knack to doing this (I
always forget!) and easy to loose the dual palette. Perhaps something along
these lines is occurring for you.

Regards,
Peter T

"AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
news:[email protected]...
> No, but good point.
>
> In this case the code resides in the current workbook so ActiveWorkbook
and
> ThisWorkbook are the same.
>
> Thanks for the thought.
>
> A co-worker said he had a similar situation with Excel workbooks created
by
> Business Objects. Opening the workbook allowed viewing the report but
> trying to print or do a print preview generated an error. Clicking on the
> worksheet tab "fixed" the problem. Essentially they are maunally doing an
> Activeworksheet.Select to work around the issue.
>
>

I don't see how a dual palette can be used since changing the color of an
index immediately changes the color throughout the workbook. But, there is
a lot I don't know and can't imagine!

Now, this has become even more confusing the code below (much of it is
redundant) was executed in two ways, each with similar but different
results.

First it was executed in the Immediate Window; one step at a time. Both
ActiveWorkbook and ThisWorkbook were used. No change since the code
effectively resides in the ActiveWorkbook. The results were:
16711680
32
255
255
255
(ActiveSheet.Select performed here)
16711680
16711680
Color Test

Then a new workbook was manually created, a module inserted, and the code
was copied to a subroutine. The Workbooks.Add code was commented out.
Before executing a second subroutine was created consisting of three lines
of code
Debug.Print ActiveSheet.Range("A1").Font.Color
ActiveSheet.Select
Debug.Print ActiveSheet.Range("A1").Font.Color

These lines of code exist within the first subroutine so I would think the
results would be the same.


The results were:

First Routine, notice that the Font Color was reported as 255 (red) five
times in a row, unlike in the Immediate Window.
16711680
255
32
255
255
255
(ActiveSheet.Select performed here)
255
255
Color Test

Second Routine, oops.
16711680
255

(I'm sending the rest of my hair to Microsoft, but if they are not careful I
will send them my first born later.



'=================== Start of code
'Start a new workbook being sure to start with the default colors and a
known worksheet.
Workbooks.Add Template:="Workbook"
ActiveWorkbook.ResetColors
Activeworkbook.Sheets(1).select
Activeworkbook.Sheets(1).activate
Activeworkbook.Sheets(1).Name = "Color Test"

'place the word Color in A1
ActiveSheet.Range("A1").value = "Color"

'Make the text of a size that color can be more readily seen.
Activesheet.Range("A1").Font.Size = 14
Activesheet.Range("A1").Font.Bold = True

'Change the font color of A1 to the color in index 32. The font is now
blue.
Activesheet.Range("A1").Font.ColorIndex = 32

'Report the Font Color in the cell (FF0000, or 16711680; ie Blue)
debug.Print Activesheet.Range("A1").Font.Color

'change the color of Index 32 to Red. The font is now red.
ActiveWorkbook.colors(32) = &HFF

'Report the ColorIndex value. (32)
debug.Print Activesheet.Range("A1").Font.ColorIndex

'Report the Color of the ColorIndex 32 (FF, ie Red)
debug.Print ActiveWorkbook.Colors(32)

'Report the Font Color in the cell (FF, or 255; ie Red.... huh?)
debug.Print Activesheet.Range("A1").Font.Color

'Report the Font Color in the cell again (FF, or 255; )
debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color

'Now, select the ActiveSheet, just for fun
Activesheet.select

'Report the Font Color in the cell again (FF0000, or 16711680; )
debug.Print Activesheet.Range("A1").Font.Color

'Ok, what is the font color now?
debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color

'Do a bit of testing
debug.Print Thisworkbook.ActiveSheet.name
'=================== End of code



--
My handle should tell you enough about me. I am not an MVP, expert, guru,
etc. but I do like to help.


"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> So what's the actual code you use.
>
> #32 in a default palette is 100% blue.
>
> It is possible to create a workbook that sustains two unique palettes
> concurrently, one default and one customized, each viewable in different
> windows of the same workbook. There's a bit of a knack to doing this (I
> always forget!) and easy to loose the dual palette. Perhaps something
along
> these lines is occurring for you.
>
> Regards,
> Peter T
>
> "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> news:[email protected]...
> > No, but good point.
> >
> > In this case the code resides in the current workbook so ActiveWorkbook
> and
> > ThisWorkbook are the same.
> >
> > Thanks for the thought.
> >
> > A co-worker said he had a similar situation with Excel workbooks created
> by
> > Business Objects. Opening the workbook allowed viewing the report but
> > trying to print or do a print preview generated an error. Clicking on
the
> > worksheet tab "fixed" the problem. Essentially they are maunally doing
an
> > Activeworksheet.Select to work around the issue.
> >
> >
>
>

I get similar results but only when I start by stepping through with F8

Sub test()

' compare difference in debug results between
' step through with F8 & run with F5

Workbooks.Add

For i = 1 To 16
Cells(i, 1).Font.ColorIndex = i
Next

For i = 1 To 16
ActiveWorkbook.Colors(i) = 255
Next

For i = 1 To 16
' should return : i 255 255
' though stepping through returns: i default palette color(i) 255

Debug.Print i, Cells(i, 1).Font.Color, _
ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex)
Next

Dim r As Range
Set r = [a6]
'put a break on next line and put cursor over r.Font.Color
x = r.Font.Color
' tip under cursor reads = 65536 (yellow) but in Locals r.font = 255 as
expected

End Sub


I've spent considerable time working with the Excel palette and still don't
fully understand it's deep inner workings. Appears to belong to the
workbook's "windows" object, which itself is a rather odd thing. But where
or how is the "default" palette stored & defined. Doing certain things with
the palette can crash Excel (albeit in rare scenarios).


> I don't see how a dual palette can be used since changing the color of an
> index immediately changes the color throughout the workbook.

One of those can't-be-possible-but-is things. With two windows, apply
colours to cells in one window and see different colours from the "other"
palette update in same cells in the other window. Switch windows and the
drop down palette changes.

Regards,
Peter T

"AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
news:[email protected]...
> I don't see how a dual palette can be used since changing the color of an
> index immediately changes the color throughout the workbook. But, there
is
> a lot I don't know and can't imagine!
>
> Now, this has become even more confusing the code below (much of it is
> redundant) was executed in two ways, each with similar but different
> results.
>
> First it was executed in the Immediate Window; one step at a time. Both
> ActiveWorkbook and ThisWorkbook were used. No change since the code
> effectively resides in the ActiveWorkbook. The results were:
> 16711680
> 32
> 255
> 255
> 255
> (ActiveSheet.Select performed here)
> 16711680
> 16711680
> Color Test
>
> Then a new workbook was manually created, a module inserted, and the code
> was copied to a subroutine. The Workbooks.Add code was commented out.
> Before executing a second subroutine was created consisting of three lines
> of code
> Debug.Print ActiveSheet.Range("A1").Font.Color
> ActiveSheet.Select
> Debug.Print ActiveSheet.Range("A1").Font.Color
>
> These lines of code exist within the first subroutine so I would think the
> results would be the same.
>
>
> The results were:
>
> First Routine, notice that the Font Color was reported as 255 (red) five
> times in a row, unlike in the Immediate Window.
> 16711680
> 255
> 32
> 255
> 255
> 255
> (ActiveSheet.Select performed here)
> 255
> 255
> Color Test
>
> Second Routine, oops.
> 16711680
> 255
>
> (I'm sending the rest of my hair to Microsoft, but if they are not careful
I
> will send them my first born later.
>
>
>
> '=================== Start of code
> 'Start a new workbook being sure to start with the default colors and a
> known worksheet.
> Workbooks.Add Template:="Workbook"
> ActiveWorkbook.ResetColors
> Activeworkbook.Sheets(1).select
> Activeworkbook.Sheets(1).activate
> Activeworkbook.Sheets(1).Name = "Color Test"
>
> 'place the word Color in A1
> ActiveSheet.Range("A1").value = "Color"
>
> 'Make the text of a size that color can be more readily seen.
> Activesheet.Range("A1").Font.Size = 14
> Activesheet.Range("A1").Font.Bold = True
>
> 'Change the font color of A1 to the color in index 32. The font is now
> blue.
> Activesheet.Range("A1").Font.ColorIndex = 32
>
> 'Report the Font Color in the cell (FF0000, or 16711680; ie Blue)
> debug.Print Activesheet.Range("A1").Font.Color
>
> 'change the color of Index 32 to Red. The font is now red.
> ActiveWorkbook.colors(32) = &HFF
>
> 'Report the ColorIndex value. (32)
> debug.Print Activesheet.Range("A1").Font.ColorIndex
>
> 'Report the Color of the ColorIndex 32 (FF, ie Red)
> debug.Print ActiveWorkbook.Colors(32)
>
> 'Report the Font Color in the cell (FF, or 255; ie Red.... huh?)
> debug.Print Activesheet.Range("A1").Font.Color
>
> 'Report the Font Color in the cell again (FF, or 255; )
> debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color
>
> 'Now, select the ActiveSheet, just for fun
> Activesheet.select
>
> 'Report the Font Color in the cell again (FF0000, or 16711680; )
> debug.Print Activesheet.Range("A1").Font.Color
>
> 'Ok, what is the font color now?
> debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color
>
> 'Do a bit of testing
> debug.Print Thisworkbook.ActiveSheet.name
> '=================== End of code
>
>
>
> --
> My handle should tell you enough about me. I am not an MVP, expert, guru,
> etc. but I do like to help.
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:[email protected]...
> > So what's the actual code you use.
> >
> > #32 in a default palette is 100% blue.
> >
> > It is possible to create a workbook that sustains two unique palettes
> > concurrently, one default and one customized, each viewable in different
> > windows of the same workbook. There's a bit of a knack to doing this (I
> > always forget!) and easy to loose the dual palette. Perhaps something
> along
> > these lines is occurring for you.
> >
> > Regards,
> > Peter T
> >
> > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> > news:[email protected]...
> > > No, but good point.
> > >
> > > In this case the code resides in the current workbook so
ActiveWorkbook
> > and
> > > ThisWorkbook are the same.
> > >
> > > Thanks for the thought.
> > >
> > > A co-worker said he had a similar situation with Excel workbooks
created
> > by
> > > Business Objects. Opening the workbook allowed viewing the report but
> > > trying to print or do a print preview generated an error. Clicking on
> the
> > > worksheet tab "fixed" the problem. Essentially they are maunally
doing
> an
> > > Activeworksheet.Select to work around the issue.
> > >
> > >
> >
> >
>
>

I found a reference and consolidated it into:
First row from left to right: 1, 53, 52, 51, 49, 11, 55, 56
second row: 9, 46, 12, 10, 14, 5, 47, 16
third row: 3, 45, 43, 50, 42, 41, 13, 48
fourth row: 7, 44, 6, 4, 8, 33, 54, 15
fifth row: 38, 40, 36, 35, 34, 37, 39, 2
sixth row first default row for charts: 17, 18, 19, 20, 21, 22, 23, 24
seventh row. second default row for charts: 25, 26, 27, 28, 29, 30, 31, 32

[a6] was set to Index 6, which is the 4th row, 3rd column, which is yellow.
Thus, you have shown the similar pattern of a change in values based on
executing code and code executed after a break. Seemingly regardless of
whether the break was a break point or an End statement.

I modified your code slightly.
' Workbooks.Add
ActiveWorkbook.ResetColors

'x = r.Font.Color
Debug.Print ActiveSheet.Range("A6").Font.Color
Debug.Print ActiveSheet.Range("A6").Font.Color '<== Add Breakpoint
ActiveSheet.Select
Debug.Print ActiveSheet.Range("A6").Font.Color


The results are the same, of course. If the code is run non-stop then all
three Debug.Prints display the value 255. If a breakpoint is placed on the
second Debug.Print statement then the first returns 255, the second 65535,
and the third 255.



So, it does seem that adding an Activesheet.Select statment provides a
possible workaround. Perhaps such a silly line of code should be documented
to prevent a rational person from removing such a ridiculous. The comment
needs to state that setting a breakpoint will give different results and
explain why. Sure wish I knew why.



At least we found what Mr. Bean did prior to becoming a comedian.


--
My handle should tell you enough about me. I am not an MVP, expert, guru,
etc. but I do like to help.


"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> I get similar results but only when I start by stepping through with F8
>
> Sub test()
>
> ' compare difference in debug results between
> ' step through with F8 & run with F5
>
> Workbooks.Add
>
> For i = 1 To 16
> Cells(i, 1).Font.ColorIndex = i
> Next
>
> For i = 1 To 16
> ActiveWorkbook.Colors(i) = 255
> Next
>
> For i = 1 To 16
> ' should return : i 255 255
> ' though stepping through returns: i default palette color(i) 255
>
> Debug.Print i, Cells(i, 1).Font.Color, _
> ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex)
> Next
>
> Dim r As Range
> Set r = [a6]
> 'put a break on next line and put cursor over r.Font.Color
> x = r.Font.Color
> ' tip under cursor reads = 65536 (yellow) but in Locals r.font = 255 as
> expected
>
> End Sub
>
>
> I've spent considerable time working with the Excel palette and still
don't
> fully understand it's deep inner workings. Appears to belong to the
> workbook's "windows" object, which itself is a rather odd thing. But where
> or how is the "default" palette stored & defined. Doing certain things
with
> the palette can crash Excel (albeit in rare scenarios).
>
>
> > I don't see how a dual palette can be used since changing the color of
an
> > index immediately changes the color throughout the workbook.
>
> One of those can't-be-possible-but-is things. With two windows, apply
> colours to cells in one window and see different colours from the "other"
> palette update in same cells in the other window. Switch windows and the
> drop down palette changes.
>
> Regards,
> Peter T
>
> "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> news:[email protected]...
> > I don't see how a dual palette can be used since changing the color of
an
> > index immediately changes the color throughout the workbook. But, there
> is
> > a lot I don't know and can't imagine!
> >
> > Now, this has become even more confusing the code below (much of it is
> > redundant) was executed in two ways, each with similar but different
> > results.
> >
> > First it was executed in the Immediate Window; one step at a time. Both
> > ActiveWorkbook and ThisWorkbook were used. No change since the code
> > effectively resides in the ActiveWorkbook. The results were:
> > 16711680
> > 32
> > 255
> > 255
> > 255
> > (ActiveSheet.Select performed here)
> > 16711680
> > 16711680
> > Color Test
> >
> > Then a new workbook was manually created, a module inserted, and the
code
> > was copied to a subroutine. The Workbooks.Add code was commented out.
> > Before executing a second subroutine was created consisting of three
lines
> > of code
> > Debug.Print ActiveSheet.Range("A1").Font.Color
> > ActiveSheet.Select
> > Debug.Print ActiveSheet.Range("A1").Font.Color
> >
> > These lines of code exist within the first subroutine so I would think
the
> > results would be the same.
> >
> >
> > The results were:
> >
> > First Routine, notice that the Font Color was reported as 255 (red) five
> > times in a row, unlike in the Immediate Window.
> > 16711680
> > 255
> > 32
> > 255
> > 255
> > 255
> > (ActiveSheet.Select performed here)
> > 255
> > 255
> > Color Test
> >
> > Second Routine, oops.
> > 16711680
> > 255
> >
> > (I'm sending the rest of my hair to Microsoft, but if they are not
careful
> I
> > will send them my first born later.
> >
> >
> >
> > '=================== Start of code
> > 'Start a new workbook being sure to start with the default colors and a
> > known worksheet.
> > Workbooks.Add Template:="Workbook"
> > ActiveWorkbook.ResetColors
> > Activeworkbook.Sheets(1).select
> > Activeworkbook.Sheets(1).activate
> > Activeworkbook.Sheets(1).Name = "Color Test"
> >
> > 'place the word Color in A1
> > ActiveSheet.Range("A1").value = "Color"
> >
> > 'Make the text of a size that color can be more readily seen.
> > Activesheet.Range("A1").Font.Size = 14
> > Activesheet.Range("A1").Font.Bold = True
> >
> > 'Change the font color of A1 to the color in index 32. The font is now
> > blue.
> > Activesheet.Range("A1").Font.ColorIndex = 32
> >
> > 'Report the Font Color in the cell (FF0000, or 16711680; ie Blue)
> > debug.Print Activesheet.Range("A1").Font.Color
> >
> > 'change the color of Index 32 to Red. The font is now red.
> > ActiveWorkbook.colors(32) = &HFF
> >
> > 'Report the ColorIndex value. (32)
> > debug.Print Activesheet.Range("A1").Font.ColorIndex
> >
> > 'Report the Color of the ColorIndex 32 (FF, ie Red)
> > debug.Print ActiveWorkbook.Colors(32)
> >
> > 'Report the Font Color in the cell (FF, or 255; ie Red.... huh?)
> > debug.Print Activesheet.Range("A1").Font.Color
> >
> > 'Report the Font Color in the cell again (FF, or 255; )
> > debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color
> >
> > 'Now, select the ActiveSheet, just for fun
> > Activesheet.select
> >
> > 'Report the Font Color in the cell again (FF0000, or 16711680; )
> > debug.Print Activesheet.Range("A1").Font.Color
> >
> > 'Ok, what is the font color now?
> > debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color
> >
> > 'Do a bit of testing
> > debug.Print Thisworkbook.ActiveSheet.name
> > '=================== End of code
> >
> >
> >
> > --
> > My handle should tell you enough about me. I am not an MVP, expert,
guru,
> > etc. but I do like to help.
> >
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:[email protected]...
> > > So what's the actual code you use.
> > >
> > > #32 in a default palette is 100% blue.
> > >
> > > It is possible to create a workbook that sustains two unique palettes
> > > concurrently, one default and one customized, each viewable in
different
> > > windows of the same workbook. There's a bit of a knack to doing this
(I
> > > always forget!) and easy to loose the dual palette. Perhaps something
> > along
> > > these lines is occurring for you.
> > >
> > > Regards,
> > > Peter T
> > >
> > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> > > news:[email protected]...
> > > > No, but good point.
> > > >
> > > > In this case the code resides in the current workbook so
> ActiveWorkbook
> > > and
> > > > ThisWorkbook are the same.
> > > >
> > > > Thanks for the thought.
> > > >
> > > > A co-worker said he had a similar situation with Excel workbooks
> created
> > > by
> > > > Business Objects. Opening the workbook allowed viewing the report
but
> > > > trying to print or do a print preview generated an error. Clicking
on
> > the
> > > > worksheet tab "fixed" the problem. Essentially they are maunally
> doing
> > an
> > > > Activeworksheet.Select to work around the issue.
> > > >
> > > >
> > >
> > >
> >
> >
>
>

If I interpret what you say correctly everything returns as expected when
you run the code normally. So I don't see the need to "workaround" by
selecting the activesheet which is not something to do for no good reason.

In normal use you can reliably return .font.color. Or in both normal & debug
mode

idx = cell.font.colorindex

if idx > 0 then
colorvalue = cell.parent.parent.colors(idx) ' ie cell's Workbook.Colors(idx)
' else
' automatic/system black, most typically colorvalue = 0
end if

Regards,
Peter T

"AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
news:[email protected]...
> I found a reference and consolidated it into:
> First row from left to right: 1, 53, 52, 51, 49, 11, 55, 56
> second row: 9, 46, 12, 10, 14, 5, 47, 16
> third row: 3, 45, 43, 50, 42, 41, 13, 48
> fourth row: 7, 44, 6, 4, 8, 33, 54, 15
> fifth row: 38, 40, 36, 35, 34, 37, 39, 2
> sixth row first default row for charts: 17, 18, 19, 20, 21, 22, 23, 24
> seventh row. second default row for charts: 25, 26, 27, 28, 29, 30, 31,
32
>
> [a6] was set to Index 6, which is the 4th row, 3rd column, which is
yellow.
> Thus, you have shown the similar pattern of a change in values based on
> executing code and code executed after a break. Seemingly regardless of
> whether the break was a break point or an End statement.
>
> I modified your code slightly.
> ' Workbooks.Add
> ActiveWorkbook.ResetColors
>
> 'x = r.Font.Color
> Debug.Print ActiveSheet.Range("A6").Font.Color
> Debug.Print ActiveSheet.Range("A6").Font.Color '<== Add Breakpoint
> ActiveSheet.Select
> Debug.Print ActiveSheet.Range("A6").Font.Color
>
>
> The results are the same, of course. If the code is run non-stop then all
> three Debug.Prints display the value 255. If a breakpoint is placed on
the
> second Debug.Print statement then the first returns 255, the second 65535,
> and the third 255.
>
>
>
> So, it does seem that adding an Activesheet.Select statment provides a
> possible workaround. Perhaps such a silly line of code should be
documented
> to prevent a rational person from removing such a ridiculous. The comment
> needs to state that setting a breakpoint will give different results and
> explain why. Sure wish I knew why.
>
>
>
> At least we found what Mr. Bean did prior to becoming a comedian.
>
>
> --
> My handle should tell you enough about me. I am not an MVP, expert, guru,
> etc. but I do like to help.
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:[email protected]...
> > I get similar results but only when I start by stepping through with F8
> >
> > Sub test()
> >
> > ' compare difference in debug results between
> > ' step through with F8 & run with F5
> >
> > Workbooks.Add
> >
> > For i = 1 To 16
> > Cells(i, 1).Font.ColorIndex = i
> > Next
> >
> > For i = 1 To 16
> > ActiveWorkbook.Colors(i) = 255
> > Next
> >
> > For i = 1 To 16
> > ' should return : i 255 255
> > ' though stepping through returns: i default palette color(i) 255
> >
> > Debug.Print i, Cells(i, 1).Font.Color, _
> > ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex)
> > Next
> >
> > Dim r As Range
> > Set r = [a6]
> > 'put a break on next line and put cursor over r.Font.Color
> > x = r.Font.Color
> > ' tip under cursor reads = 65536 (yellow) but in Locals r.font = 255 as
> > expected
> >
> > End Sub
> >
> >
> > I've spent considerable time working with the Excel palette and still
> don't
> > fully understand it's deep inner workings. Appears to belong to the
> > workbook's "windows" object, which itself is a rather odd thing. But
where
> > or how is the "default" palette stored & defined. Doing certain things
> with
> > the palette can crash Excel (albeit in rare scenarios).
> >
> >
> > > I don't see how a dual palette can be used since changing the color of
> an
> > > index immediately changes the color throughout the workbook.
> >
> > One of those can't-be-possible-but-is things. With two windows, apply
> > colours to cells in one window and see different colours from the
"other"
> > palette update in same cells in the other window. Switch windows and the
> > drop down palette changes.
> >
> > Regards,
> > Peter T
> >
> > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> > news:[email protected]...
> > > I don't see how a dual palette can be used since changing the color of
> an
> > > index immediately changes the color throughout the workbook. But,
there
> > is
> > > a lot I don't know and can't imagine!
> > >
> > > Now, this has become even more confusing the code below (much of it is
> > > redundant) was executed in two ways, each with similar but different
> > > results.
> > >
> > > First it was executed in the Immediate Window; one step at a time.
Both
> > > ActiveWorkbook and ThisWorkbook were used. No change since the code
> > > effectively resides in the ActiveWorkbook. The results were:
> > > 16711680
> > > 32
> > > 255
> > > 255
> > > 255
> > > (ActiveSheet.Select performed here)
> > > 16711680
> > > 16711680
> > > Color Test
> > >
> > > Then a new workbook was manually created, a module inserted, and the
> code
> > > was copied to a subroutine. The Workbooks.Add code was commented out.
> > > Before executing a second subroutine was created consisting of three
> lines
> > > of code
> > > Debug.Print ActiveSheet.Range("A1").Font.Color
> > > ActiveSheet.Select
> > > Debug.Print ActiveSheet.Range("A1").Font.Color
> > >
> > > These lines of code exist within the first subroutine so I would think
> the
> > > results would be the same.
> > >
> > >
> > > The results were:
> > >
> > > First Routine, notice that the Font Color was reported as 255 (red)
five
> > > times in a row, unlike in the Immediate Window.
> > > 16711680
> > > 255
> > > 32
> > > 255
> > > 255
> > > 255
> > > (ActiveSheet.Select performed here)
> > > 255
> > > 255
> > > Color Test
> > >
> > > Second Routine, oops.
> > > 16711680
> > > 255
> > >
> > > (I'm sending the rest of my hair to Microsoft, but if they are not
> careful
> > I
> > > will send them my first born later.
> > >
> > >
> > >
> > > '=================== Start of code
> > > 'Start a new workbook being sure to start with the default colors and
a
> > > known worksheet.
> > > Workbooks.Add Template:="Workbook"
> > > ActiveWorkbook.ResetColors
> > > Activeworkbook.Sheets(1).select
> > > Activeworkbook.Sheets(1).activate
> > > Activeworkbook.Sheets(1).Name = "Color Test"
> > >
> > > 'place the word Color in A1
> > > ActiveSheet.Range("A1").value = "Color"
> > >
> > > 'Make the text of a size that color can be more readily seen.
> > > Activesheet.Range("A1").Font.Size = 14
> > > Activesheet.Range("A1").Font.Bold = True
> > >
> > > 'Change the font color of A1 to the color in index 32. The font is
now
> > > blue.
> > > Activesheet.Range("A1").Font.ColorIndex = 32
> > >
> > > 'Report the Font Color in the cell (FF0000, or 16711680; ie Blue)
> > > debug.Print Activesheet.Range("A1").Font.Color
> > >
> > > 'change the color of Index 32 to Red. The font is now red.
> > > ActiveWorkbook.colors(32) = &HFF
> > >
> > > 'Report the ColorIndex value. (32)
> > > debug.Print Activesheet.Range("A1").Font.ColorIndex
> > >
> > > 'Report the Color of the ColorIndex 32 (FF, ie Red)
> > > debug.Print ActiveWorkbook.Colors(32)
> > >
> > > 'Report the Font Color in the cell (FF, or 255; ie Red.... huh?)
> > > debug.Print Activesheet.Range("A1").Font.Color
> > >
> > > 'Report the Font Color in the cell again (FF, or 255; )
> > > debug.Print Thisworkbook.WorkSheets("Color
Test").Range("A1").Font.Color
> > >
> > > 'Now, select the ActiveSheet, just for fun
> > > Activesheet.select
> > >
> > > 'Report the Font Color in the cell again (FF0000, or 16711680; )
> > > debug.Print Activesheet.Range("A1").Font.Color
> > >
> > > 'Ok, what is the font color now?
> > > debug.Print Thisworkbook.WorkSheets("Color
Test").Range("A1").Font.Color
> > >
> > > 'Do a bit of testing
> > > debug.Print Thisworkbook.ActiveSheet.name
> > > '=================== End of code
> > >
> > >
> > >
> > > --
> > > My handle should tell you enough about me. I am not an MVP, expert,
> guru,
> > > etc. but I do like to help.
> > >
> > >
> > > "Peter T" <peter_t@discussions> wrote in message
> > > news:[email protected]...
> > > > So what's the actual code you use.
> > > >
> > > > #32 in a default palette is 100% blue.
> > > >
> > > > It is possible to create a workbook that sustains two unique
palettes
> > > > concurrently, one default and one customized, each viewable in
> different
> > > > windows of the same workbook. There's a bit of a knack to doing this
> (I
> > > > always forget!) and easy to loose the dual palette. Perhaps
something
> > > along
> > > > these lines is occurring for you.
> > > >
> > > > Regards,
> > > > Peter T
> > > >
> > > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> > > > news:[email protected]...
> > > > > No, but good point.
> > > > >
> > > > > In this case the code resides in the current workbook so
> > ActiveWorkbook
> > > > and
> > > > > ThisWorkbook are the same.
> > > > >
> > > > > Thanks for the thought.
> > > > >
> > > > > A co-worker said he had a similar situation with Excel workbooks
> > created
> > > > by
> > > > > Business Objects. Opening the workbook allowed viewing the report
> but
> > > > > trying to print or do a print preview generated an error.
Clicking
> on
> > > the
> > > > > worksheet tab "fixed" the problem. Essentially they are maunally
> > doing
> > > an
> > > > > Activeworksheet.Select to work around the issue.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

>everything returns as expected when you run the code normally
Only if the code is always sets colors whenever they are to be tested,
including events.


Thanks to your help we have a better, although a very slightly slower, work
around that always works. The "ActiveSheet.Select" work around fails if the
code is interrupted between the selection and test and it may interfere with
code working with multiple worksheets.

The proper work around is find the color of the color index of the color.

To find the Interior color of cell "A1" use:
With ActiveSheet.Range("A1").Interior
If .ColorIndex < 0 Then
lngcolor = .Color
Else
lngcolor = ActiveWorkbook.Colors(.ColorIndex)
End If
End With

To find the Font color used in cell "A1" use:
'Warning: This code assumes all characters within the cell use the same font
color!
With ActiveSheet.Range("A1").Font
If .ColorIndex < 0 Then
lngcolor = .Color
Else
lngcolor = ActiveWorkbook.Colors(.ColorIndex)
End If
End With



Some code examples use the .Color directly and others that use .ColorIndex
directly. Both have problems.

ColorIndex is fine as long as it is not the Color that is important but the
ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is one
example.

Color is fine as long as the ColorIndex has not been modified prior to the
current execution.

Thanks for hashing this out with me.


PS. I normally define and set worksheet and workbook objects then use those
in my code. This was not tested and may have some impact, although I"m sure
the final solution will work without problems.


--
My handle should tell you enough about me. I am not an MVP, expert, guru,
etc. but I do like to help.


"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> If I interpret what you say correctly everything returns as expected when
> you run the code normally. So I don't see the need to "workaround" by
> selecting the activesheet which is not something to do for no good reason.
>
> In normal use you can reliably return .font.color. Or in both normal &
debug
> mode
>
> idx = cell.font.colorindex
>
> if idx > 0 then
> colorvalue = cell.parent.parent.colors(idx) ' ie cell's
Workbook.Colors(idx)
> ' else
> ' automatic/system black, most typically colorvalue = 0
> end if
>
> Regards,
> Peter T
>
> "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> news:[email protected]...
> > I found a reference and consolidated it into:
> > First row from left to right: 1, 53, 52, 51, 49, 11, 55, 56
> > second row: 9, 46, 12, 10, 14, 5, 47, 16
> > third row: 3, 45, 43, 50, 42, 41, 13, 48
> > fourth row: 7, 44, 6, 4, 8, 33, 54, 15
> > fifth row: 38, 40, 36, 35, 34, 37, 39, 2
> > sixth row first default row for charts: 17, 18, 19, 20, 21, 22, 23, 24
> > seventh row. second default row for charts: 25, 26, 27, 28, 29, 30, 31,
> 32
> >
> > [a6] was set to Index 6, which is the 4th row, 3rd column, which is
> yellow.
> > Thus, you have shown the similar pattern of a change in values based on
> > executing code and code executed after a break. Seemingly regardless of
> > whether the break was a break point or an End statement.
> >
> > I modified your code slightly.
> > ' Workbooks.Add
> > ActiveWorkbook.ResetColors
> >
> > 'x = r.Font.Color
> > Debug.Print ActiveSheet.Range("A6").Font.Color
> > Debug.Print ActiveSheet.Range("A6").Font.Color '<== Add
Breakpoint
> > ActiveSheet.Select
> > Debug.Print ActiveSheet.Range("A6").Font.Color
> >
> >
> > The results are the same, of course. If the code is run non-stop then
all
> > three Debug.Prints display the value 255. If a breakpoint is placed on
> the
> > second Debug.Print statement then the first returns 255, the second
65535,
> > and the third 255.
> >
> >
> >
> > So, it does seem that adding an Activesheet.Select statment provides a
> > possible workaround. Perhaps such a silly line of code should be
> documented
> > to prevent a rational person from removing such a ridiculous. The
comment
> > needs to state that setting a breakpoint will give different results and
> > explain why. Sure wish I knew why.
> >
> >
> >
> > At least we found what Mr. Bean did prior to becoming a comedian.
> >
> >
> > --
> > My handle should tell you enough about me. I am not an MVP, expert,
guru,
> > etc. but I do like to help.
> >
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:[email protected]...
> > > I get similar results but only when I start by stepping through with
F8
> > >
> > > Sub test()
> > >
> > > ' compare difference in debug results between
> > > ' step through with F8 & run with F5
> > >
> > > Workbooks.Add
> > >
> > > For i = 1 To 16
> > > Cells(i, 1).Font.ColorIndex = i
> > > Next
> > >
> > > For i = 1 To 16
> > > ActiveWorkbook.Colors(i) = 255
> > > Next
> > >
> > > For i = 1 To 16
> > > ' should return : i 255 255
> > > ' though stepping through returns: i default palette color(i) 255
> > >
> > > Debug.Print i, Cells(i, 1).Font.Color, _
> > > ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex)
> > > Next
> > >
> > > Dim r As Range
> > > Set r = [a6]
> > > 'put a break on next line and put cursor over r.Font.Color
> > > x = r.Font.Color
> > > ' tip under cursor reads = 65536 (yellow) but in Locals r.font = 255
as
> > > expected
> > >
> > > End Sub
> > >
> > >
> > > I've spent considerable time working with the Excel palette and still
> > don't
> > > fully understand it's deep inner workings. Appears to belong to the
> > > workbook's "windows" object, which itself is a rather odd thing. But
> where
> > > or how is the "default" palette stored & defined. Doing certain things
> > with
> > > the palette can crash Excel (albeit in rare scenarios).
> > >
> > >
> > > > I don't see how a dual palette can be used since changing the color
of
> > an
> > > > index immediately changes the color throughout the workbook.
> > >
> > > One of those can't-be-possible-but-is things. With two windows, apply
> > > colours to cells in one window and see different colours from the
> "other"
> > > palette update in same cells in the other window. Switch windows and
the
> > > drop down palette changes.
> > >
> > > Regards,
> > > Peter T
> > >
> > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> > > news:[email protected]...
> > > > I don't see how a dual palette can be used since changing the color
of
> > an
> > > > index immediately changes the color throughout the workbook. But,
> there
> > > is
> > > > a lot I don't know and can't imagine!
> > > >
> > > > Now, this has become even more confusing the code below (much of it
is
> > > > redundant) was executed in two ways, each with similar but different
> > > > results.
> > > >
> > > > First it was executed in the Immediate Window; one step at a time.
> Both
> > > > ActiveWorkbook and ThisWorkbook were used. No change since the code
> > > > effectively resides in the ActiveWorkbook. The results were:
> > > > 16711680
> > > > 32
> > > > 255
> > > > 255
> > > > 255
> > > > (ActiveSheet.Select performed here)
> > > > 16711680
> > > > 16711680
> > > > Color Test
> > > >
> > > > Then a new workbook was manually created, a module inserted, and the
> > code
> > > > was copied to a subroutine. The Workbooks.Add code was commented
out.
> > > > Before executing a second subroutine was created consisting of three
> > lines
> > > > of code
> > > > Debug.Print ActiveSheet.Range("A1").Font.Color
> > > > ActiveSheet.Select
> > > > Debug.Print ActiveSheet.Range("A1").Font.Color
> > > >
> > > > These lines of code exist within the first subroutine so I would
think
> > the
> > > > results would be the same.
> > > >
> > > >
> > > > The results were:
> > > >
> > > > First Routine, notice that the Font Color was reported as 255 (red)
> five
> > > > times in a row, unlike in the Immediate Window.
> > > > 16711680
> > > > 255
> > > > 32
> > > > 255
> > > > 255
> > > > 255
> > > > (ActiveSheet.Select performed here)
> > > > 255
> > > > 255
> > > > Color Test
> > > >
> > > > Second Routine, oops.
> > > > 16711680
> > > > 255
> > > >
> > > > (I'm sending the rest of my hair to Microsoft, but if they are not
> > careful
> > > I
> > > > will send them my first born later.
> > > >
> > > >
> > > >
> > > > '=================== Start of code
> > > > 'Start a new workbook being sure to start with the default colors
and
> a
> > > > known worksheet.
> > > > Workbooks.Add Template:="Workbook"
> > > > ActiveWorkbook.ResetColors
> > > > Activeworkbook.Sheets(1).select
> > > > Activeworkbook.Sheets(1).activate
> > > > Activeworkbook.Sheets(1).Name = "Color Test"
> > > >
> > > > 'place the word Color in A1
> > > > ActiveSheet.Range("A1").value = "Color"
> > > >
> > > > 'Make the text of a size that color can be more readily seen.
> > > > Activesheet.Range("A1").Font.Size = 14
> > > > Activesheet.Range("A1").Font.Bold = True
> > > >
> > > > 'Change the font color of A1 to the color in index 32. The font is
> now
> > > > blue.
> > > > Activesheet.Range("A1").Font.ColorIndex = 32
> > > >
> > > > 'Report the Font Color in the cell (FF0000, or 16711680; ie
Blue)
> > > > debug.Print Activesheet.Range("A1").Font.Color
> > > >
> > > > 'change the color of Index 32 to Red. The font is now red.
> > > > ActiveWorkbook.colors(32) = &HFF
> > > >
> > > > 'Report the ColorIndex value. (32)
> > > > debug.Print Activesheet.Range("A1").Font.ColorIndex
> > > >
> > > > 'Report the Color of the ColorIndex 32 (FF, ie Red)
> > > > debug.Print ActiveWorkbook.Colors(32)
> > > >
> > > > 'Report the Font Color in the cell (FF, or 255; ie Red....
huh?)
> > > > debug.Print Activesheet.Range("A1").Font.Color
> > > >
> > > > 'Report the Font Color in the cell again (FF, or 255; )
> > > > debug.Print Thisworkbook.WorkSheets("Color
> Test").Range("A1").Font.Color
> > > >
> > > > 'Now, select the ActiveSheet, just for fun
> > > > Activesheet.select
> > > >
> > > > 'Report the Font Color in the cell again (FF0000, or
16711680; )
> > > > debug.Print Activesheet.Range("A1").Font.Color
> > > >
> > > > 'Ok, what is the font color now?
> > > > debug.Print Thisworkbook.WorkSheets("Color
> Test").Range("A1").Font.Color
> > > >
> > > > 'Do a bit of testing
> > > > debug.Print Thisworkbook.ActiveSheet.name
> > > > '=================== End of code
> > > >
> > > >
> > > >
> > > > --
> > > > My handle should tell you enough about me. I am not an MVP, expert,
> > guru,
> > > > etc. but I do like to help.
> > > >
> > > >
> > > > "Peter T" <peter_t@discussions> wrote in message
> > > > news:[email protected]...
> > > > > So what's the actual code you use.
> > > > >
> > > > > #32 in a default palette is 100% blue.
> > > > >
> > > > > It is possible to create a workbook that sustains two unique
> palettes
> > > > > concurrently, one default and one customized, each viewable in
> > different
> > > > > windows of the same workbook. There's a bit of a knack to doing
this
> > (I
> > > > > always forget!) and easy to loose the dual palette. Perhaps
> something
> > > > along
> > > > > these lines is occurring for you.
> > > > >
> > > > > Regards,
> > > > > Peter T
> > > > >
> > > > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in
message
> > > > > news:[email protected]...
> > > > > > No, but good point.
> > > > > >
> > > > > > In this case the code resides in the current workbook so
> > > ActiveWorkbook
> > > > > and
> > > > > > ThisWorkbook are the same.
> > > > > >
> > > > > > Thanks for the thought.
> > > > > >
> > > > > > A co-worker said he had a similar situation with Excel workbooks
> > > created
> > > > > by
> > > > > > Business Objects. Opening the workbook allowed viewing the
report
> > but
> > > > > > trying to print or do a print preview generated an error.
> Clicking
> > on
> > > > the
> > > > > > worksheet tab "fixed" the problem. Essentially they are
maunally
> > > doing
> > > > an
> > > > > > Activeworksheet.Select to work around the issue.
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Glad you've got it working

> 'Warning: This code assumes all characters within the cell use the same
font
> color!

Dim vx as variant

vx = .colorindex

if isnull(vx) then
' it's mixed colours
elseif vdx < 0
' automatic
else
a palette colour
end if

> Some code examples use the .Color directly and others that use .ColorIndex
> directly. Both have problems.
>
> ColorIndex is fine as long as it is not the Color that is important but
the
> ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is one
> example.

In a default palette there are 10 duplicates, in a customized who knows. But
I don't see why you say it's a problem to return the colour.

> Color is fine as long as the ColorIndex has not been modified prior to the
> current execution.

Unlike you, the problem discussed earlier only occurs for me in debug / step
mode.

Regards,
Peter T



"AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
news:[email protected]...
> >everything returns as expected when you run the code normally
> Only if the code is always sets colors whenever they are to be tested,
> including events.
>
>
> Thanks to your help we have a better, although a very slightly slower,
work
> around that always works. The "ActiveSheet.Select" work around fails if
the
> code is interrupted between the selection and test and it may interfere
with
> code working with multiple worksheets.
>
> The proper work around is find the color of the color index of the color.
>
> To find the Interior color of cell "A1" use:
> With ActiveSheet.Range("A1").Interior
> If .ColorIndex < 0 Then
> lngcolor = .Color
> Else
> lngcolor = ActiveWorkbook.Colors(.ColorIndex)
> End If
> End With
>
> To find the Font color used in cell "A1" use:
> 'Warning: This code assumes all characters within the cell use the same
font
> color!
> With ActiveSheet.Range("A1").Font
> If .ColorIndex < 0 Then
> lngcolor = .Color
> Else
> lngcolor = ActiveWorkbook.Colors(.ColorIndex)
> End If
> End With
>
>
>
> Some code examples use the .Color directly and others that use .ColorIndex
> directly. Both have problems.
>
> ColorIndex is fine as long as it is not the Color that is important but
the
> ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is one
> example.
>
> Color is fine as long as the ColorIndex has not been modified prior to the
> current execution.
>
> Thanks for hashing this out with me.
>
>
> PS. I normally define and set worksheet and workbook objects then use
those
> in my code. This was not tested and may have some impact, although I"m
sure
> the final solution will work without problems.
>
>
> --
> My handle should tell you enough about me. I am not an MVP, expert, guru,
> etc. but I do like to help.
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:[email protected]...
> > If I interpret what you say correctly everything returns as expected
when
> > you run the code normally. So I don't see the need to "workaround" by
> > selecting the activesheet which is not something to do for no good
reason.
> >
> > In normal use you can reliably return .font.color. Or in both normal &
> debug
> > mode
> >
> > idx = cell.font.colorindex
> >
> > if idx > 0 then
> > colorvalue = cell.parent.parent.colors(idx) ' ie cell's
> Workbook.Colors(idx)
> > ' else
> > ' automatic/system black, most typically colorvalue = 0
> > end if
> >
> > Regards,
> > Peter T
> >
> > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> > news:[email protected]...
> > > I found a reference and consolidated it into:
> > > First row from left to right: 1, 53, 52, 51, 49, 11, 55, 56
> > > second row: 9, 46, 12, 10, 14, 5, 47, 16
> > > third row: 3, 45, 43, 50, 42, 41, 13, 48
> > > fourth row: 7, 44, 6, 4, 8, 33, 54, 15
> > > fifth row: 38, 40, 36, 35, 34, 37, 39, 2
> > > sixth row first default row for charts: 17, 18, 19, 20, 21, 22, 23,
24
> > > seventh row. second default row for charts: 25, 26, 27, 28, 29, 30,
31,
> > 32
> > >
> > > [a6] was set to Index 6, which is the 4th row, 3rd column, which is
> > yellow.
> > > Thus, you have shown the similar pattern of a change in values based
on
> > > executing code and code executed after a break. Seemingly regardless
of
> > > whether the break was a break point or an End statement.
> > >
> > > I modified your code slightly.
> > > ' Workbooks.Add
> > > ActiveWorkbook.ResetColors
> > >
> > > 'x = r.Font.Color
> > > Debug.Print ActiveSheet.Range("A6").Font.Color
> > > Debug.Print ActiveSheet.Range("A6").Font.Color '<== Add
> Breakpoint
> > > ActiveSheet.Select
> > > Debug.Print ActiveSheet.Range("A6").Font.Color
> > >
> > >
> > > The results are the same, of course. If the code is run non-stop then
> all
> > > three Debug.Prints display the value 255. If a breakpoint is placed
on
> > the
> > > second Debug.Print statement then the first returns 255, the second
> 65535,
> > > and the third 255.
> > >
> > >
> > >
> > > So, it does seem that adding an Activesheet.Select statment provides a
> > > possible workaround. Perhaps such a silly line of code should be
> > documented
> > > to prevent a rational person from removing such a ridiculous. The
> comment
> > > needs to state that setting a breakpoint will give different results
and
> > > explain why. Sure wish I knew why.
> > >
> > >
> > >
> > > At least we found what Mr. Bean did prior to becoming a comedian.
> > >
> > >
> > > --
> > > My handle should tell you enough about me. I am not an MVP, expert,
> guru,
> > > etc. but I do like to help.
> > >
> > >
> > > "Peter T" <peter_t@discussions> wrote in message
> > > news:[email protected]...
> > > > I get similar results but only when I start by stepping through with
> F8
> > > >
> > > > Sub test()
> > > >
> > > > ' compare difference in debug results between
> > > > ' step through with F8 & run with F5
> > > >
> > > > Workbooks.Add
> > > >
> > > > For i = 1 To 16
> > > > Cells(i, 1).Font.ColorIndex = i
> > > > Next
> > > >
> > > > For i = 1 To 16
> > > > ActiveWorkbook.Colors(i) = 255
> > > > Next
> > > >
> > > > For i = 1 To 16
> > > > ' should return : i 255 255
> > > > ' though stepping through returns: i default palette color(i)
255
> > > >
> > > > Debug.Print i, Cells(i, 1).Font.Color, _
> > > > ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex)
> > > > Next
> > > >
> > > > Dim r As Range
> > > > Set r = [a6]
> > > > 'put a break on next line and put cursor over r.Font.Color
> > > > x = r.Font.Color
> > > > ' tip under cursor reads = 65536 (yellow) but in Locals r.font = 255
> as
> > > > expected
> > > >
> > > > End Sub
> > > >
> > > >
> > > > I've spent considerable time working with the Excel palette and
still
> > > don't
> > > > fully understand it's deep inner workings. Appears to belong to the
> > > > workbook's "windows" object, which itself is a rather odd thing. But
> > where
> > > > or how is the "default" palette stored & defined. Doing certain
things
> > > with
> > > > the palette can crash Excel (albeit in rare scenarios).
> > > >
> > > >
> > > > > I don't see how a dual palette can be used since changing the
color
> of
> > > an
> > > > > index immediately changes the color throughout the workbook.
> > > >
> > > > One of those can't-be-possible-but-is things. With two windows,
apply
> > > > colours to cells in one window and see different colours from the
> > "other"
> > > > palette update in same cells in the other window. Switch windows and
> the
> > > > drop down palette changes.
> > > >
> > > > Regards,
> > > > Peter T
> > > >
> > > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> > > > news:[email protected]...
> > > > > I don't see how a dual palette can be used since changing the
color
> of
> > > an
> > > > > index immediately changes the color throughout the workbook. But,
> > there
> > > > is
> > > > > a lot I don't know and can't imagine!
> > > > >
> > > > > Now, this has become even more confusing the code below (much of
it
> is
> > > > > redundant) was executed in two ways, each with similar but
different
> > > > > results.
> > > > >
> > > > > First it was executed in the Immediate Window; one step at a time.
> > Both
> > > > > ActiveWorkbook and ThisWorkbook were used. No change since the
code
> > > > > effectively resides in the ActiveWorkbook. The results were:
> > > > > 16711680
> > > > > 32
> > > > > 255
> > > > > 255
> > > > > 255
> > > > > (ActiveSheet.Select performed here)
> > > > > 16711680
> > > > > 16711680
> > > > > Color Test
> > > > >
> > > > > Then a new workbook was manually created, a module inserted, and
the
> > > code
> > > > > was copied to a subroutine. The Workbooks.Add code was commented
> out.
> > > > > Before executing a second subroutine was created consisting of
three
> > > lines
> > > > > of code
> > > > > Debug.Print ActiveSheet.Range("A1").Font.Color
> > > > > ActiveSheet.Select
> > > > > Debug.Print ActiveSheet.Range("A1").Font.Color
> > > > >
> > > > > These lines of code exist within the first subroutine so I would
> think
> > > the
> > > > > results would be the same.
> > > > >
> > > > >
> > > > > The results were:
> > > > >
> > > > > First Routine, notice that the Font Color was reported as 255
(red)
> > five
> > > > > times in a row, unlike in the Immediate Window.
> > > > > 16711680
> > > > > 255
> > > > > 32
> > > > > 255
> > > > > 255
> > > > > 255
> > > > > (ActiveSheet.Select performed here)
> > > > > 255
> > > > > 255
> > > > > Color Test
> > > > >
> > > > > Second Routine, oops.
> > > > > 16711680
> > > > > 255
> > > > >
> > > > > (I'm sending the rest of my hair to Microsoft, but if they are not
> > > careful
> > > > I
> > > > > will send them my first born later.
> > > > >
> > > > >
> > > > >
> > > > > '=================== Start of code
> > > > > 'Start a new workbook being sure to start with the default colors
> and
> > a
> > > > > known worksheet.
> > > > > Workbooks.Add Template:="Workbook"
> > > > > ActiveWorkbook.ResetColors
> > > > > Activeworkbook.Sheets(1).select
> > > > > Activeworkbook.Sheets(1).activate
> > > > > Activeworkbook.Sheets(1).Name = "Color Test"
> > > > >
> > > > > 'place the word Color in A1
> > > > > ActiveSheet.Range("A1").value = "Color"
> > > > >
> > > > > 'Make the text of a size that color can be more readily seen.
> > > > > Activesheet.Range("A1").Font.Size = 14
> > > > > Activesheet.Range("A1").Font.Bold = True
> > > > >
> > > > > 'Change the font color of A1 to the color in index 32. The font
is
> > now
> > > > > blue.
> > > > > Activesheet.Range("A1").Font.ColorIndex = 32
> > > > >
> > > > > 'Report the Font Color in the cell (FF0000, or 16711680; ie
> Blue)
> > > > > debug.Print Activesheet.Range("A1").Font.Color
> > > > >
> > > > > 'change the color of Index 32 to Red. The font is now red.
> > > > > ActiveWorkbook.colors(32) = &HFF
> > > > >
> > > > > 'Report the ColorIndex value. (32)
> > > > > debug.Print Activesheet.Range("A1").Font.ColorIndex
> > > > >
> > > > > 'Report the Color of the ColorIndex 32 (FF, ie Red)
> > > > > debug.Print ActiveWorkbook.Colors(32)
> > > > >
> > > > > 'Report the Font Color in the cell (FF, or 255; ie Red....
> huh?)
> > > > > debug.Print Activesheet.Range("A1").Font.Color
> > > > >
> > > > > 'Report the Font Color in the cell again (FF, or 255; )
> > > > > debug.Print Thisworkbook.WorkSheets("Color
> > Test").Range("A1").Font.Color
> > > > >
> > > > > 'Now, select the ActiveSheet, just for fun
> > > > > Activesheet.select
> > > > >
> > > > > 'Report the Font Color in the cell again (FF0000, or
> 16711680; )
> > > > > debug.Print Activesheet.Range("A1").Font.Color
> > > > >
> > > > > 'Ok, what is the font color now?
> > > > > debug.Print Thisworkbook.WorkSheets("Color
> > Test").Range("A1").Font.Color
> > > > >
> > > > > 'Do a bit of testing
> > > > > debug.Print Thisworkbook.ActiveSheet.name
> > > > > '=================== End of code
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > My handle should tell you enough about me. I am not an MVP,
expert,
> > > guru,
> > > > > etc. but I do like to help.
> > > > >
> > > > >
> > > > > "Peter T" <peter_t@discussions> wrote in message
> > > > > news:[email protected]...
> > > > > > So what's the actual code you use.
> > > > > >
> > > > > > #32 in a default palette is 100% blue.
> > > > > >
> > > > > > It is possible to create a workbook that sustains two unique
> > palettes
> > > > > > concurrently, one default and one customized, each viewable in
> > > different
> > > > > > windows of the same workbook. There's a bit of a knack to doing
> this
> > > (I
> > > > > > always forget!) and easy to loose the dual palette. Perhaps
> > something
> > > > > along
> > > > > > these lines is occurring for you.
> > > > > >
> > > > > > Regards,
> > > > > > Peter T
> > > > > >
> > > > > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in
> message
> > > > > > news:[email protected]...
> > > > > > > No, but good point.
> > > > > > >
> > > > > > > In this case the code resides in the current workbook so
> > > > ActiveWorkbook
> > > > > > and
> > > > > > > ThisWorkbook are the same.
> > > > > > >
> > > > > > > Thanks for the thought.
> > > > > > >
> > > > > > > A co-worker said he had a similar situation with Excel
workbooks
> > > > created
> > > > > > by
> > > > > > > Business Objects. Opening the workbook allowed viewing the
> report
> > > but
> > > > > > > trying to print or do a print preview generated an error.
> > Clicking
> > > on
> > > > > the
> > > > > > > worksheet tab "fixed" the problem. Essentially they are
> maunally
> > > > doing
> > > > > an
> > > > > > > Activeworksheet.Select to work around the issue.
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

The isnull is nice to know, thanks!

> I don't see why you say it's a problem to return the colour.
because the Font.color or interior.color can return either the default or
actual color.... depending.



> Unlike you, the problem discussed earlier only occurs for me in debug /
step
> mode.

Use this code in a new workbook.

Sub SetDuringExecution()
ActiveWorkbook.ResetColors
ActiveWorkbook.Colors(6) = &HFF
ActiveSheet.Range("A1").Font.ColorIndex = 6
Debug.Print "------"
Debug.Print ActiveSheet.Range("A1").Font.ColorIndex
Debug.Print ActiveSheet.Range("A1").Font.Color
Debug.Print
ActiveWorkbook.Colors(ActiveSheet.Range("A1").Font.ColorIndex)
End Sub
Sub SetInAPriorExecution()
Debug.Print "------"
Debug.Print ActiveSheet.Range("A1").Font.ColorIndex
Debug.Print ActiveSheet.Range("A1").Font.Color
Debug.Print
ActiveWorkbook.Colors(ActiveSheet.Range("A1").Font.ColorIndex)
End Sub

Execute the first routine (set during)
The result should be.
------
6
255
255

Execute the second routine (set in a prior)
The result should be.
------
6
255
255

This is what I think you are referring to.

Now, toggle to the actual worksheet. You can do what you want to the
worksheet, or do nothing.
Now, execute the second routine again.
The result should be.
------
6
65535
255

Let me know if your results are different or the same. PLEASE! I'm using
Excel 2002 on XP 2002 at work the code works as demonstrated at work. I am
using Excel 2000 on Win 2K at home and believe it worked the same at home,
but will retest with this exact code to be sure. I will only mention the
results if they are different.


6 is, of course, the color index. Default is yellow as you know.
The second number is either 255 (actual) or 65535 (default). This reports
the font color.
The third number is always the actual color. It reports the color of the
color index. Thus, I suggest this is the proper code, once the negative and
null values are handled. In my case, the value will never be null, but good
to know.



--
My handle should tell you enough about me. I am not an MVP, expert, guru,
etc. but I do like to help.


"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> Glad you've got it working
>
> > 'Warning: This code assumes all characters within the cell use the same
> font
> > color!
>
> Dim vx as variant
>
> vx = .colorindex
>
> if isnull(vx) then
> ' it's mixed colours
> elseif vdx < 0
> ' automatic
> else
> a palette colour
> end if
>
> > Some code examples use the .Color directly and others that use
..ColorIndex
> > directly. Both have problems.
> >
> > ColorIndex is fine as long as it is not the Color that is important but
> the
> > ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is one
> > example.
>
> In a default palette there are 10 duplicates, in a customized who knows.
But
> I don't see why you say it's a problem to return the colour.
>
> > Color is fine as long as the ColorIndex has not been modified prior to
the
> > current execution.
>
> Unlike you, the problem discussed earlier only occurs for me in debug /
step
> mode.
>
> Regards,
> Peter T
>
>
>
> "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> news:[email protected]...
> > >everything returns as expected when you run the code normally
> > Only if the code is always sets colors whenever they are to be tested,
> > including events.
> >
> >
> > Thanks to your help we have a better, although a very slightly slower,
> work
> > around that always works. The "ActiveSheet.Select" work around fails if
> the
> > code is interrupted between the selection and test and it may interfere
> with
> > code working with multiple worksheets.
> >
> > The proper work around is find the color of the color index of the
color.
> >
> > To find the Interior color of cell "A1" use:
> > With ActiveSheet.Range("A1").Interior
> > If .ColorIndex < 0 Then
> > lngcolor = .Color
> > Else
> > lngcolor = ActiveWorkbook.Colors(.ColorIndex)
> > End If
> > End With
> >
> > To find the Font color used in cell "A1" use:
> > 'Warning: This code assumes all characters within the cell use the same
> font
> > color!
> > With ActiveSheet.Range("A1").Font
> > If .ColorIndex < 0 Then
> > lngcolor = .Color
> > Else
> > lngcolor = ActiveWorkbook.Colors(.ColorIndex)
> > End If
> > End With
> >
> >
> >
> > Some code examples use the .Color directly and others that use
..ColorIndex
> > directly. Both have problems.
> >
> > ColorIndex is fine as long as it is not the Color that is important but
> the
> > ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is one
> > example.
> >
> > Color is fine as long as the ColorIndex has not been modified prior to
the
> > current execution.
> >
> > Thanks for hashing this out with me.
> >
> >
> > PS. I normally define and set worksheet and workbook objects then use
> those
> > in my code. This was not tested and may have some impact, although I"m
> sure
> > the final solution will work without problems.
> >
> >
> > --
> > My handle should tell you enough about me. I am not an MVP, expert,
guru,
> > etc. but I do like to help.
> >
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:[email protected]...
> > > If I interpret what you say correctly everything returns as expected
> when
> > > you run the code normally. So I don't see the need to "workaround" by
> > > selecting the activesheet which is not something to do for no good
> reason.
> > >
> > > In normal use you can reliably return .font.color. Or in both normal &
> > debug
> > > mode
> > >
> > > idx = cell.font.colorindex
> > >
> > > if idx > 0 then
> > > colorvalue = cell.parent.parent.colors(idx) ' ie cell's
> > Workbook.Colors(idx)
> > > ' else
> > > ' automatic/system black, most typically colorvalue = 0
> > > end if
> > >
> > > Regards,
> > > Peter T
> > >
> > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> > > news:[email protected]...
> > > > I found a reference and consolidated it into:
> > > > First row from left to right: 1, 53, 52, 51, 49, 11, 55, 56
> > > > second row: 9, 46, 12, 10, 14, 5, 47, 16
> > > > third row: 3, 45, 43, 50, 42, 41, 13, 48
> > > > fourth row: 7, 44, 6, 4, 8, 33, 54, 15
> > > > fifth row: 38, 40, 36, 35, 34, 37, 39, 2
> > > > sixth row first default row for charts: 17, 18, 19, 20, 21, 22, 23,
> 24
> > > > seventh row. second default row for charts: 25, 26, 27, 28, 29, 30,
> 31,
> > > 32
> > > >
> > > > [a6] was set to Index 6, which is the 4th row, 3rd column, which is
> > > yellow.
> > > > Thus, you have shown the similar pattern of a change in values based
> on
> > > > executing code and code executed after a break. Seemingly
regardless
> of
> > > > whether the break was a break point or an End statement.
> > > >
> > > > I modified your code slightly.
> > > > ' Workbooks.Add
> > > > ActiveWorkbook.ResetColors
> > > >
> > > > 'x = r.Font.Color
> > > > Debug.Print ActiveSheet.Range("A6").Font.Color
> > > > Debug.Print ActiveSheet.Range("A6").Font.Color '<== Add
> > Breakpoint
> > > > ActiveSheet.Select
> > > > Debug.Print ActiveSheet.Range("A6").Font.Color
> > > >
> > > >
> > > > The results are the same, of course. If the code is run non-stop
then
> > all
> > > > three Debug.Prints display the value 255. If a breakpoint is placed
> on
> > > the
> > > > second Debug.Print statement then the first returns 255, the second
> > 65535,
> > > > and the third 255.
> > > >
> > > >
> > > >
> > > > So, it does seem that adding an Activesheet.Select statment provides
a
> > > > possible workaround. Perhaps such a silly line of code should be
> > > documented
> > > > to prevent a rational person from removing such a ridiculous. The
> > comment
> > > > needs to state that setting a breakpoint will give different results
> and
> > > > explain why. Sure wish I knew why.
> > > >
> > > >
> > > >
> > > > At least we found what Mr. Bean did prior to becoming a comedian.
> > > >
> > > >
> > > > --
> > > > My handle should tell you enough about me. I am not an MVP, expert,
> > guru,
> > > > etc. but I do like to help.
> > > >
> > > >
> > > > "Peter T" <peter_t@discussions> wrote in message
> > > > news:[email protected]...
> > > > > I get similar results but only when I start by stepping through
with
> > F8
> > > > >
> > > > > Sub test()
> > > > >
> > > > > ' compare difference in debug results between
> > > > > ' step through with F8 & run with F5
> > > > >
> > > > > Workbooks.Add
> > > > >
> > > > > For i = 1 To 16
> > > > > Cells(i, 1).Font.ColorIndex = i
> > > > > Next
> > > > >
> > > > > For i = 1 To 16
> > > > > ActiveWorkbook.Colors(i) = 255
> > > > > Next
> > > > >
> > > > > For i = 1 To 16
> > > > > ' should return : i 255 255
> > > > > ' though stepping through returns: i default palette color(i)
> 255
> > > > >
> > > > > Debug.Print i, Cells(i, 1).Font.Color, _
> > > > > ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex)
> > > > > Next
> > > > >
> > > > > Dim r As Range
> > > > > Set r = [a6]
> > > > > 'put a break on next line and put cursor over r.Font.Color
> > > > > x = r.Font.Color
> > > > > ' tip under cursor reads = 65536 (yellow) but in Locals r.font =
255
> > as
> > > > > expected
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > > I've spent considerable time working with the Excel palette and
> still
> > > > don't
> > > > > fully understand it's deep inner workings. Appears to belong to
the
> > > > > workbook's "windows" object, which itself is a rather odd thing.
But
> > > where
> > > > > or how is the "default" palette stored & defined. Doing certain
> things
> > > > with
> > > > > the palette can crash Excel (albeit in rare scenarios).
> > > > >
> > > > >
> > > > > > I don't see how a dual palette can be used since changing the
> color
> > of
> > > > an
> > > > > > index immediately changes the color throughout the workbook.
> > > > >
> > > > > One of those can't-be-possible-but-is things. With two windows,
> apply
> > > > > colours to cells in one window and see different colours from the
> > > "other"
> > > > > palette update in same cells in the other window. Switch windows
and
> > the
> > > > > drop down palette changes.
> > > > >
> > > > > Regards,
> > > > > Peter T
> > > > >
> > > > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in
message
> > > > > news:[email protected]...
> > > > > > I don't see how a dual palette can be used since changing the
> color
> > of
> > > > an
> > > > > > index immediately changes the color throughout the workbook.
But,
> > > there
> > > > > is
> > > > > > a lot I don't know and can't imagine!
> > > > > >
> > > > > > Now, this has become even more confusing the code below (much of
> it
> > is
> > > > > > redundant) was executed in two ways, each with similar but
> different
> > > > > > results.
> > > > > >
> > > > > > First it was executed in the Immediate Window; one step at a
time.
> > > Both
> > > > > > ActiveWorkbook and ThisWorkbook were used. No change since the
> code
> > > > > > effectively resides in the ActiveWorkbook. The results were:
> > > > > > 16711680
> > > > > > 32
> > > > > > 255
> > > > > > 255
> > > > > > 255
> > > > > > (ActiveSheet.Select performed here)
> > > > > > 16711680
> > > > > > 16711680
> > > > > > Color Test
> > > > > >
> > > > > > Then a new workbook was manually created, a module inserted, and
> the
> > > > code
> > > > > > was copied to a subroutine. The Workbooks.Add code was
commented
> > out.
> > > > > > Before executing a second subroutine was created consisting of
> three
> > > > lines
> > > > > > of code
> > > > > > Debug.Print ActiveSheet.Range("A1").Font.Color
> > > > > > ActiveSheet.Select
> > > > > > Debug.Print ActiveSheet.Range("A1").Font.Color
> > > > > >
> > > > > > These lines of code exist within the first subroutine so I would
> > think
> > > > the
> > > > > > results would be the same.
> > > > > >
> > > > > >
> > > > > > The results were:
> > > > > >
> > > > > > First Routine, notice that the Font Color was reported as 255
> (red)
> > > five
> > > > > > times in a row, unlike in the Immediate Window.
> > > > > > 16711680
> > > > > > 255
> > > > > > 32
> > > > > > 255
> > > > > > 255
> > > > > > 255
> > > > > > (ActiveSheet.Select performed here)
> > > > > > 255
> > > > > > 255
> > > > > > Color Test
> > > > > >
> > > > > > Second Routine, oops.
> > > > > > 16711680
> > > > > > 255
> > > > > >
> > > > > > (I'm sending the rest of my hair to Microsoft, but if they are
not
> > > > careful
> > > > > I
> > > > > > will send them my first born later.
> > > > > >
> > > > > >
> > > > > >
> > > > > > '=================== Start of code
> > > > > > 'Start a new workbook being sure to start with the default
colors
> > and
> > > a
> > > > > > known worksheet.
> > > > > > Workbooks.Add Template:="Workbook"
> > > > > > ActiveWorkbook.ResetColors
> > > > > > Activeworkbook.Sheets(1).select
> > > > > > Activeworkbook.Sheets(1).activate
> > > > > > Activeworkbook.Sheets(1).Name = "Color Test"
> > > > > >
> > > > > > 'place the word Color in A1
> > > > > > ActiveSheet.Range("A1").value = "Color"
> > > > > >
> > > > > > 'Make the text of a size that color can be more readily seen.
> > > > > > Activesheet.Range("A1").Font.Size = 14
> > > > > > Activesheet.Range("A1").Font.Bold = True
> > > > > >
> > > > > > 'Change the font color of A1 to the color in index 32. The font
> is
> > > now
> > > > > > blue.
> > > > > > Activesheet.Range("A1").Font.ColorIndex = 32
> > > > > >
> > > > > > 'Report the Font Color in the cell (FF0000, or 16711680; ie
> > Blue)
> > > > > > debug.Print Activesheet.Range("A1").Font.Color
> > > > > >
> > > > > > 'change the color of Index 32 to Red. The font is now red.
> > > > > > ActiveWorkbook.colors(32) = &HFF
> > > > > >
> > > > > > 'Report the ColorIndex value. (32)
> > > > > > debug.Print Activesheet.Range("A1").Font.ColorIndex
> > > > > >
> > > > > > 'Report the Color of the ColorIndex 32 (FF, ie Red)
> > > > > > debug.Print ActiveWorkbook.Colors(32)
> > > > > >
> > > > > > 'Report the Font Color in the cell (FF, or 255; ie Red....
> > huh?)
> > > > > > debug.Print Activesheet.Range("A1").Font.Color
> > > > > >
> > > > > > 'Report the Font Color in the cell again (FF, or 255; )
> > > > > > debug.Print Thisworkbook.WorkSheets("Color
> > > Test").Range("A1").Font.Color
> > > > > >
> > > > > > 'Now, select the ActiveSheet, just for fun
> > > > > > Activesheet.select
> > > > > >
> > > > > > 'Report the Font Color in the cell again (FF0000, or
> > 16711680; )
> > > > > > debug.Print Activesheet.Range("A1").Font.Color
> > > > > >
> > > > > > 'Ok, what is the font color now?
> > > > > > debug.Print Thisworkbook.WorkSheets("Color
> > > Test").Range("A1").Font.Color
> > > > > >
> > > > > > 'Do a bit of testing
> > > > > > debug.Print Thisworkbook.ActiveSheet.name
> > > > > > '=================== End of code
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > My handle should tell you enough about me. I am not an MVP,
> expert,
> > > > guru,
> > > > > > etc. but I do like to help.
> > > > > >
> > > > > >
> > > > > > "Peter T" <peter_t@discussions> wrote in message
> > > > > > news:[email protected]...
> > > > > > > So what's the actual code you use.
> > > > > > >
> > > > > > > #32 in a default palette is 100% blue.
> > > > > > >
> > > > > > > It is possible to create a workbook that sustains two unique
> > > palettes
> > > > > > > concurrently, one default and one customized, each viewable in
> > > > different
> > > > > > > windows of the same workbook. There's a bit of a knack to
doing
> > this
> > > > (I
> > > > > > > always forget!) and easy to loose the dual palette. Perhaps
> > > something
> > > > > > along
> > > > > > > these lines is occurring for you.
> > > > > > >
> > > > > > > Regards,
> > > > > > > Peter T
> > > > > > >
> > > > > > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in
> > message
> > > > > > > news:[email protected]...
> > > > > > > > No, but good point.
> > > > > > > >
> > > > > > > > In this case the code resides in the current workbook so
> > > > > ActiveWorkbook
> > > > > > > and
> > > > > > > > ThisWorkbook are the same.
> > > > > > > >
> > > > > > > > Thanks for the thought.
> > > > > > > >
> > > > > > > > A co-worker said he had a similar situation with Excel
> workbooks
> > > > > created
> > > > > > > by
> > > > > > > > Business Objects. Opening the workbook allowed viewing the
> > report
> > > > but
> > > > > > > > trying to print or do a print preview generated an error.
> > > Clicking
> > > > on
> > > > > > the
> > > > > > > > worksheet tab "fixed" the problem. Essentially they are
> > maunally
> > > > > doing
> > > > > > an
> > > > > > > > Activeworksheet.Select to work around the issue.
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Hmm - I see what you mean

To avoid any confusion I ran this in Excel with Alt-F8. No need to add a new
wb and code can be in any wb.

First customize #6 and apply #6 to A1 then comment those lines

Sub Test3()
Dim n(1 To 3, 1 To 3) As Long

'customize #6 one time only
ActiveWorkbook.Colors(6) = 255

' apply Font.ColorIndex first time only
ActiveSheet.Range("A1").Font.ColorIndex = 6

With ActiveSheet.Range("A1").Font ' or .Interior

' apply Font.ColorIndex first time only, then comment
' .ColorIndex = 6

n(1, 1) = .ColorIndex
n(2, 1) = .Color ' 65535 - wrong (unless format applied above)
n(3, 1) = ActiveWorkbook.Colors(.ColorIndex)

' do something to "any" cell format - not necessarily A1
.ColorIndex = .ColorIndex '
' .Bold = .Bold ' only if "With .Font"

n(1, 2) = .ColorIndex
n(2, 2) = .Color ' 255 - right
n(3, 2) = ActiveWorkbook.Colors(.ColorIndex)

[a1] = [a1] + 1 ' change a value

n(1, 3) = .ColorIndex
n(2, 3) = .Color ' 65535 - wrong again !!
n(3, 3) = ActiveWorkbook.Colors(.ColorIndex)

[b1:d3].Value = n

End With

End Sub

Conclusion - return the colour of the colorindex.
Or apply some format (even same format), but as Font can have mixed formats
best to apply to .Interior.

Regards,
Peter T


"AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
news:eO31#[email protected]...
> The isnull is nice to know, thanks!
>
> > I don't see why you say it's a problem to return the colour.
> because the Font.color or interior.color can return either the default or
> actual color.... depending.
>
>
>
> > Unlike you, the problem discussed earlier only occurs for me in debug /
> step
> > mode.
>
> Use this code in a new workbook.
>
> Sub SetDuringExecution()
> ActiveWorkbook.ResetColors
> ActiveWorkbook.Colors(6) = &HFF
> ActiveSheet.Range("A1").Font.ColorIndex = 6
> Debug.Print "------"
> Debug.Print ActiveSheet.Range("A1").Font.ColorIndex
> Debug.Print ActiveSheet.Range("A1").Font.Color
> Debug.Print
> ActiveWorkbook.Colors(ActiveSheet.Range("A1").Font.ColorIndex)
> End Sub
> Sub SetInAPriorExecution()
> Debug.Print "------"
> Debug.Print ActiveSheet.Range("A1").Font.ColorIndex
> Debug.Print ActiveSheet.Range("A1").Font.Color
> Debug.Print
> ActiveWorkbook.Colors(ActiveSheet.Range("A1").Font.ColorIndex)
> End Sub
>
> Execute the first routine (set during)
> The result should be.
> ------
> 6
> 255
> 255
>
> Execute the second routine (set in a prior)
> The result should be.
> ------
> 6
> 255
> 255
>
> This is what I think you are referring to.
>
> Now, toggle to the actual worksheet. You can do what you want to the
> worksheet, or do nothing.
> Now, execute the second routine again.
> The result should be.
> ------
> 6
> 65535
> 255
>
> Let me know if your results are different or the same. PLEASE! I'm
using
> Excel 2002 on XP 2002 at work the code works as demonstrated at work. I
am
> using Excel 2000 on Win 2K at home and believe it worked the same at
home,
> but will retest with this exact code to be sure. I will only mention the
> results if they are different.
>
>
> 6 is, of course, the color index. Default is yellow as you know.
> The second number is either 255 (actual) or 65535 (default). This reports
> the font color.
> The third number is always the actual color. It reports the color of the
> color index. Thus, I suggest this is the proper code, once the negative
and
> null values are handled. In my case, the value will never be null, but
good
> to know.
>
>
>
> --
> My handle should tell you enough about me. I am not an MVP, expert, guru,
> etc. but I do like to help.
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:[email protected]...
> > Glad you've got it working
> >
> > > 'Warning: This code assumes all characters within the cell use the
same
> > font
> > > color!
> >
> > Dim vx as variant
> >
> > vx = .colorindex
> >
> > if isnull(vx) then
> > ' it's mixed colours
> > elseif vdx < 0
> > ' automatic
> > else
> > a palette colour
> > end if
> >
> > > Some code examples use the .Color directly and others that use
> .ColorIndex
> > > directly. Both have problems.
> > >
> > > ColorIndex is fine as long as it is not the Color that is important
but
> > the
> > > ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is
one
> > > example.
> >
> > In a default palette there are 10 duplicates, in a customized who knows.
> But
> > I don't see why you say it's a problem to return the colour.
> >
> > > Color is fine as long as the ColorIndex has not been modified prior to
> the
> > > current execution.
> >
> > Unlike you, the problem discussed earlier only occurs for me in debug /
> step
> > mode.
> >
> > Regards,
> > Peter T
> >
> >
> >
> > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> > news:[email protected]...
> > > >everything returns as expected when you run the code normally
> > > Only if the code is always sets colors whenever they are to be tested,
> > > including events.
> > >
> > >
> > > Thanks to your help we have a better, although a very slightly slower,
> > work
> > > around that always works. The "ActiveSheet.Select" work around fails
if
> > the
> > > code is interrupted between the selection and test and it may
interfere
> > with
> > > code working with multiple worksheets.
> > >
> > > The proper work around is find the color of the color index of the
> color.
> > >
> > > To find the Interior color of cell "A1" use:
> > > With ActiveSheet.Range("A1").Interior
> > > If .ColorIndex < 0 Then
> > > lngcolor = .Color
> > > Else
> > > lngcolor = ActiveWorkbook.Colors(.ColorIndex)
> > > End If
> > > End With
> > >
> > > To find the Font color used in cell "A1" use:
> > > 'Warning: This code assumes all characters within the cell use the
same
> > font
> > > color!
> > > With ActiveSheet.Range("A1").Font
> > > If .ColorIndex < 0 Then
> > > lngcolor = .Color
> > > Else
> > > lngcolor = ActiveWorkbook.Colors(.ColorIndex)
> > > End If
> > > End With
> > >
> > >
> > >
> > > Some code examples use the .Color directly and others that use
> .ColorIndex
> > > directly. Both have problems.
> > >
> > > ColorIndex is fine as long as it is not the Color that is important
but
> > the
> > > ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is
one
> > > example.
> > >
> > > Color is fine as long as the ColorIndex has not been modified prior to
> the
> > > current execution.
> > >
> > > Thanks for hashing this out with me.
> > >
> > >
> > > PS. I normally define and set worksheet and workbook objects then use
> > those
> > > in my code. This was not tested and may have some impact, although
I"m
> > sure
> > > the final solution will work without problems.
> > >
> > >
> > > --
> > > My handle should tell you enough about me. I am not an MVP, expert,
> guru,
> > > etc. but I do like to help.
> > >
> > >
> > > "Peter T" <peter_t@discussions> wrote in message
> > > news:[email protected]...
> > > > If I interpret what you say correctly everything returns as expected
> > when
> > > > you run the code normally. So I don't see the need to "workaround"
by
> > > > selecting the activesheet which is not something to do for no good
> > reason.
> > > >
> > > > In normal use you can reliably return .font.color. Or in both normal
&
> > > debug
> > > > mode
> > > >
> > > > idx = cell.font.colorindex
> > > >
> > > > if idx > 0 then
> > > > colorvalue = cell.parent.parent.colors(idx) ' ie cell's
> > > Workbook.Colors(idx)
> > > > ' else
> > > > ' automatic/system black, most typically colorvalue = 0
> > > > end if
> > > >
> > > > Regards,
> > > > Peter T
> > > >
> > > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> > > > news:[email protected]...
> > > > > I found a reference and consolidated it into:
> > > > > First row from left to right: 1, 53, 52, 51, 49, 11, 55, 56
> > > > > second row: 9, 46, 12, 10, 14, 5, 47, 16
> > > > > third row: 3, 45, 43, 50, 42, 41, 13, 48
> > > > > fourth row: 7, 44, 6, 4, 8, 33, 54, 15
> > > > > fifth row: 38, 40, 36, 35, 34, 37, 39, 2
> > > > > sixth row first default row for charts: 17, 18, 19, 20, 21, 22,
23,
> > 24
> > > > > seventh row. second default row for charts: 25, 26, 27, 28, 29,
30,
> > 31,
> > > > 32
> > > > >
> > > > > [a6] was set to Index 6, which is the 4th row, 3rd column, which
is
> > > > yellow.
> > > > > Thus, you have shown the similar pattern of a change in values
based
> > on
> > > > > executing code and code executed after a break. Seemingly
> regardless
> > of
> > > > > whether the break was a break point or an End statement.
> > > > >
> > > > > I modified your code slightly.
> > > > > ' Workbooks.Add
> > > > > ActiveWorkbook.ResetColors
> > > > >
> > > > > 'x = r.Font.Color
> > > > > Debug.Print ActiveSheet.Range("A6").Font.Color
> > > > > Debug.Print ActiveSheet.Range("A6").Font.Color '<== Add
> > > Breakpoint
> > > > > ActiveSheet.Select
> > > > > Debug.Print ActiveSheet.Range("A6").Font.Color
> > > > >
> > > > >
> > > > > The results are the same, of course. If the code is run non-stop
> then
> > > all
> > > > > three Debug.Prints display the value 255. If a breakpoint is
placed
> > on
> > > > the
> > > > > second Debug.Print statement then the first returns 255, the
second
> > > 65535,
> > > > > and the third 255.
> > > > >
> > > > >
> > > > >
> > > > > So, it does seem that adding an Activesheet.Select statment
provides
> a
> > > > > possible workaround. Perhaps such a silly line of code should be
> > > > documented
> > > > > to prevent a rational person from removing such a ridiculous. The
> > > comment
> > > > > needs to state that setting a breakpoint will give different
results
> > and
> > > > > explain why. Sure wish I knew why.
> > > > >
> > > > >
> > > > >
> > > > > At least we found what Mr. Bean did prior to becoming a comedian.
> > > > >
> > > > >
> > > > > --
> > > > > My handle should tell you enough about me. I am not an MVP,
expert,
> > > guru,
> > > > > etc. but I do like to help.
> > > > >
> > > > >
> > > > > "Peter T" <peter_t@discussions> wrote in message
> > > > > news:[email protected]...
> > > > > > I get similar results but only when I start by stepping through
> with
> > > F8
> > > > > >
> > > > > > Sub test()
> > > > > >
> > > > > > ' compare difference in debug results between
> > > > > > ' step through with F8 & run with F5
> > > > > >
> > > > > > Workbooks.Add
> > > > > >
> > > > > > For i = 1 To 16
> > > > > > Cells(i, 1).Font.ColorIndex = i
> > > > > > Next
> > > > > >
> > > > > > For i = 1 To 16
> > > > > > ActiveWorkbook.Colors(i) = 255
> > > > > > Next
> > > > > >
> > > > > > For i = 1 To 16
> > > > > > ' should return : i 255 255
> > > > > > ' though stepping through returns: i default palette color(i)
> > 255
> > > > > >
> > > > > > Debug.Print i, Cells(i, 1).Font.Color, _
> > > > > > ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex)
> > > > > > Next
> > > > > >
> > > > > > Dim r As Range
> > > > > > Set r = [a6]
> > > > > > 'put a break on next line and put cursor over r.Font.Color
> > > > > > x = r.Font.Color
> > > > > > ' tip under cursor reads = 65536 (yellow) but in Locals r.font =
> 255
> > > as
> > > > > > expected
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > > I've spent considerable time working with the Excel palette and
> > still
> > > > > don't
> > > > > > fully understand it's deep inner workings. Appears to belong to
> the
> > > > > > workbook's "windows" object, which itself is a rather odd thing.
> But
> > > > where
> > > > > > or how is the "default" palette stored & defined. Doing certain
> > things
> > > > > with
> > > > > > the palette can crash Excel (albeit in rare scenarios).
> > > > > >
> > > > > >
> > > > > > > I don't see how a dual palette can be used since changing the
> > color
> > > of
> > > > > an
> > > > > > > index immediately changes the color throughout the workbook.
> > > > > >
> > > > > > One of those can't-be-possible-but-is things. With two windows,
> > apply
> > > > > > colours to cells in one window and see different colours from
the
> > > > "other"
> > > > > > palette update in same cells in the other window. Switch windows
> and
> > > the
> > > > > > drop down palette changes.
> > > > > >
> > > > > > Regards,
> > > > > > Peter T
> > > > > >
> > > > > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in
> message
> > > > > > news:[email protected]...
> > > > > > > I don't see how a dual palette can be used since changing the
> > color
> > > of
> > > > > an
> > > > > > > index immediately changes the color throughout the workbook.
> But,
> > > > there
> > > > > > is
> > > > > > > a lot I don't know and can't imagine!
> > > > > > >
> > > > > > > Now, this has become even more confusing the code below (much
of
> > it
> > > is
> > > > > > > redundant) was executed in two ways, each with similar but
> > different
> > > > > > > results.
> > > > > > >
> > > > > > > First it was executed in the Immediate Window; one step at a
> time.
> > > > Both
> > > > > > > ActiveWorkbook and ThisWorkbook were used. No change since
the
> > code
> > > > > > > effectively resides in the ActiveWorkbook. The results were:
> > > > > > > 16711680
> > > > > > > 32
> > > > > > > 255
> > > > > > > 255
> > > > > > > 255
> > > > > > > (ActiveSheet.Select performed here)
> > > > > > > 16711680
> > > > > > > 16711680
> > > > > > > Color Test
> > > > > > >
> > > > > > > Then a new workbook was manually created, a module inserted,
and
> > the
> > > > > code
> > > > > > > was copied to a subroutine. The Workbooks.Add code was
> commented
> > > out.
> > > > > > > Before executing a second subroutine was created consisting of
> > three
> > > > > lines
> > > > > > > of code
> > > > > > > Debug.Print ActiveSheet.Range("A1").Font.Color
> > > > > > > ActiveSheet.Select
> > > > > > > Debug.Print ActiveSheet.Range("A1").Font.Color
> > > > > > >
> > > > > > > These lines of code exist within the first subroutine so I
would
> > > think
> > > > > the
> > > > > > > results would be the same.
> > > > > > >
> > > > > > >
> > > > > > > The results were:
> > > > > > >
> > > > > > > First Routine, notice that the Font Color was reported as 255
> > (red)
> > > > five
> > > > > > > times in a row, unlike in the Immediate Window.
> > > > > > > 16711680
> > > > > > > 255
> > > > > > > 32
> > > > > > > 255
> > > > > > > 255
> > > > > > > 255
> > > > > > > (ActiveSheet.Select performed here)
> > > > > > > 255
> > > > > > > 255
> > > > > > > Color Test
> > > > > > >
> > > > > > > Second Routine, oops.
> > > > > > > 16711680
> > > > > > > 255
> > > > > > >
> > > > > > > (I'm sending the rest of my hair to Microsoft, but if they are
> not
> > > > > careful
> > > > > > I
> > > > > > > will send them my first born later.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > '=================== Start of code
> > > > > > > 'Start a new workbook being sure to start with the default
> colors
> > > and
> > > > a
> > > > > > > known worksheet.
> > > > > > > Workbooks.Add Template:="Workbook"
> > > > > > > ActiveWorkbook.ResetColors
> > > > > > > Activeworkbook.Sheets(1).select
> > > > > > > Activeworkbook.Sheets(1).activate
> > > > > > > Activeworkbook.Sheets(1).Name = "Color Test"
> > > > > > >
> > > > > > > 'place the word Color in A1
> > > > > > > ActiveSheet.Range("A1").value = "Color"
> > > > > > >
> > > > > > > 'Make the text of a size that color can be more readily seen.
> > > > > > > Activesheet.Range("A1").Font.Size = 14
> > > > > > > Activesheet.Range("A1").Font.Bold = True
> > > > > > >
> > > > > > > 'Change the font color of A1 to the color in index 32. The
font
> > is
> > > > now
> > > > > > > blue.
> > > > > > > Activesheet.Range("A1").Font.ColorIndex = 32
> > > > > > >
> > > > > > > 'Report the Font Color in the cell (FF0000, or 16711680;
ie
> > > Blue)
> > > > > > > debug.Print Activesheet.Range("A1").Font.Color
> > > > > > >
> > > > > > > 'change the color of Index 32 to Red. The font is now red.
> > > > > > > ActiveWorkbook.colors(32) = &HFF
> > > > > > >
> > > > > > > 'Report the ColorIndex value. (32)
> > > > > > > debug.Print Activesheet.Range("A1").Font.ColorIndex
> > > > > > >
> > > > > > > 'Report the Color of the ColorIndex 32 (FF, ie Red)
> > > > > > > debug.Print ActiveWorkbook.Colors(32)
> > > > > > >
> > > > > > > 'Report the Font Color in the cell (FF, or 255; ie
Red....
> > > huh?)
> > > > > > > debug.Print Activesheet.Range("A1").Font.Color
> > > > > > >
> > > > > > > 'Report the Font Color in the cell again (FF, or 255; )
> > > > > > > debug.Print Thisworkbook.WorkSheets("Color
> > > > Test").Range("A1").Font.Color
> > > > > > >
> > > > > > > 'Now, select the ActiveSheet, just for fun
> > > > > > > Activesheet.select
> > > > > > >
> > > > > > > 'Report the Font Color in the cell again (FF0000, or
> > > 16711680; )
> > > > > > > debug.Print Activesheet.Range("A1").Font.Color
> > > > > > >
> > > > > > > 'Ok, what is the font color now?
> > > > > > > debug.Print Thisworkbook.WorkSheets("Color
> > > > Test").Range("A1").Font.Color
> > > > > > >
> > > > > > > 'Do a bit of testing
> > > > > > > debug.Print Thisworkbook.ActiveSheet.name
> > > > > > > '=================== End of code
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > My handle should tell you enough about me. I am not an MVP,
> > expert,
> > > > > guru,
> > > > > > > etc. but I do like to help.
> > > > > > >
> > > > > > >
> > > > > > > "Peter T" <peter_t@discussions> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > So what's the actual code you use.
> > > > > > > >
> > > > > > > > #32 in a default palette is 100% blue.
> > > > > > > >
> > > > > > > > It is possible to create a workbook that sustains two unique
> > > > palettes
> > > > > > > > concurrently, one default and one customized, each viewable
in
> > > > > different
> > > > > > > > windows of the same workbook. There's a bit of a knack to
> doing
> > > this
> > > > > (I
> > > > > > > > always forget!) and easy to loose the dual palette. Perhaps
> > > > something
> > > > > > > along
> > > > > > > > these lines is occurring for you.
> > > > > > > >
> > > > > > > > Regards,
> > > > > > > > Peter T
> > > > > > > >
> > > > > > > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in
> > > message
> > > > > > > > news:[email protected]...
> > > > > > > > > No, but good point.
> > > > > > > > >
> > > > > > > > > In this case the code resides in the current workbook so
> > > > > > ActiveWorkbook
> > > > > > > > and
> > > > > > > > > ThisWorkbook are the same.
> > > > > > > > >
> > > > > > > > > Thanks for the thought.
> > > > > > > > >
> > > > > > > > > A co-worker said he had a similar situation with Excel
> > workbooks
> > > > > > created
> > > > > > > > by
> > > > > > > > > Business Objects. Opening the workbook allowed viewing
the
> > > report
> > > > > but
> > > > > > > > > trying to print or do a print preview generated an error.
> > > > Clicking
> > > > > on
> > > > > > > the
> > > > > > > > > worksheet tab "fixed" the problem. Essentially they are
> > > maunally
> > > > > > doing
> > > > > > > an
> > > > > > > > > Activeworksheet.Select to work around the issue.
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Well, I'm "glad" you could confirm this feature on your system. The problem
exists, as one would assume, in both Excel 2000 under Win 2K and 2002 under
Win XP.

Your tests were interesting because you were able to replicate with code
what I was producing with manual actions.

As for the conclusion
> Or apply some format (even same format), but as Font can have mixed
formats
> best to apply to .Interior.

This still breaks when code execution is stopped (debug.assert, stop, break
point, and other errors) so determining the color of the color index appears
to be the only decent solution.

Take care. Thanks for the conversation.

--
My handle should tell you enough about me. I am not an MVP, expert, guru,
etc. but I do like to help.


"Peter T" <peter_t@discussions> wrote in message
news:%[email protected]...
> Hmm - I see what you mean
>
> To avoid any confusion I ran this in Excel with Alt-F8. No need to add a
new
> wb and code can be in any wb.
>
> First customize #6 and apply #6 to A1 then comment those lines
>
> Sub Test3()
> Dim n(1 To 3, 1 To 3) As Long
>
> 'customize #6 one time only
> ActiveWorkbook.Colors(6) = 255
>
> ' apply Font.ColorIndex first time only
> ActiveSheet.Range("A1").Font.ColorIndex = 6
>
> With ActiveSheet.Range("A1").Font ' or .Interior
>
> ' apply Font.ColorIndex first time only, then comment
> ' .ColorIndex = 6
>
> n(1, 1) = .ColorIndex
> n(2, 1) = .Color ' 65535 - wrong (unless format applied above)
> n(3, 1) = ActiveWorkbook.Colors(.ColorIndex)
>
> ' do something to "any" cell format - not necessarily A1
> .ColorIndex = .ColorIndex '
> ' .Bold = .Bold ' only if "With .Font"
>
> n(1, 2) = .ColorIndex
> n(2, 2) = .Color ' 255 - right
> n(3, 2) = ActiveWorkbook.Colors(.ColorIndex)
>
> [a1] = [a1] + 1 ' change a value
>
> n(1, 3) = .ColorIndex
> n(2, 3) = .Color ' 65535 - wrong again !!
> n(3, 3) = ActiveWorkbook.Colors(.ColorIndex)
>
> [b1:d3].Value = n
>
> End With
>
> End Sub
>
> Conclusion - return the colour of the colorindex.
> Or apply some format (even same format), but as Font can have mixed
formats
> best to apply to .Interior.
>
> Regards,
> Peter T
>
>
> "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> news:eO31#[email protected]...
> > The isnull is nice to know, thanks!
> >
> > > I don't see why you say it's a problem to return the colour.
> > because the Font.color or interior.color can return either the default
or
> > actual color.... depending.
> >
> >
> >
> > > Unlike you, the problem discussed earlier only occurs for me in debug
/
> > step
> > > mode.
> >
> > Use this code in a new workbook.
> >
> > Sub SetDuringExecution()
> > ActiveWorkbook.ResetColors
> > ActiveWorkbook.Colors(6) = &HFF
> > ActiveSheet.Range("A1").Font.ColorIndex = 6
> > Debug.Print "------"
> > Debug.Print ActiveSheet.Range("A1").Font.ColorIndex
> > Debug.Print ActiveSheet.Range("A1").Font.Color
> > Debug.Print
> > ActiveWorkbook.Colors(ActiveSheet.Range("A1").Font.ColorIndex)
> > End Sub
> > Sub SetInAPriorExecution()
> > Debug.Print "------"
> > Debug.Print ActiveSheet.Range("A1").Font.ColorIndex
> > Debug.Print ActiveSheet.Range("A1").Font.Color
> > Debug.Print
> > ActiveWorkbook.Colors(ActiveSheet.Range("A1").Font.ColorIndex)
> > End Sub
> >
> > Execute the first routine (set during)
> > The result should be.
> > ------
> > 6
> > 255
> > 255
> >
> > Execute the second routine (set in a prior)
> > The result should be.
> > ------
> > 6
> > 255
> > 255
> >
> > This is what I think you are referring to.
> >
> > Now, toggle to the actual worksheet. You can do what you want to the
> > worksheet, or do nothing.
> > Now, execute the second routine again.
> > The result should be.
> > ------
> > 6
> > 65535
> > 255
> >
> > Let me know if your results are different or the same. PLEASE! I'm
> using
> > Excel 2002 on XP 2002 at work the code works as demonstrated at work. I
> am
> > using Excel 2000 on Win 2K at home and believe it worked the same at
> home,
> > but will retest with this exact code to be sure. I will only mention
the
> > results if they are different.
> >
> >
> > 6 is, of course, the color index. Default is yellow as you know.
> > The second number is either 255 (actual) or 65535 (default). This
reports
> > the font color.
> > The third number is always the actual color. It reports the color of
the
> > color index. Thus, I suggest this is the proper code, once the negative
> and
> > null values are handled. In my case, the value will never be null, but
> good
> > to know.
> >
> >
> >
> > --
> > My handle should tell you enough about me. I am not an MVP, expert,
guru,
> > etc. but I do like to help.
> >
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:[email protected]...
> > > Glad you've got it working
> > >
> > > > 'Warning: This code assumes all characters within the cell use the
> same
> > > font
> > > > color!
> > >
> > > Dim vx as variant
> > >
> > > vx = .colorindex
> > >
> > > if isnull(vx) then
> > > ' it's mixed colours
> > > elseif vdx < 0
> > > ' automatic
> > > else
> > > a palette colour
> > > end if
> > >
> > > > Some code examples use the .Color directly and others that use
> > .ColorIndex
> > > > directly. Both have problems.
> > > >
> > > > ColorIndex is fine as long as it is not the Color that is important
> but
> > > the
> > > > ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is
> one
> > > > example.
> > >
> > > In a default palette there are 10 duplicates, in a customized who
knows.
> > But
> > > I don't see why you say it's a problem to return the colour.
> > >
> > > > Color is fine as long as the ColorIndex has not been modified prior
to
> > the
> > > > current execution.
> > >
> > > Unlike you, the problem discussed earlier only occurs for me in debug
/
> > step
> > > mode.
> > >
> > > Regards,
> > > Peter T
> > >
> > >
> > >
> > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
> > > news:[email protected]...
> > > > >everything returns as expected when you run the code normally
> > > > Only if the code is always sets colors whenever they are to be
tested,
> > > > including events.
> > > >
> > > >
> > > > Thanks to your help we have a better, although a very slightly
slower,
> > > work
> > > > around that always works. The "ActiveSheet.Select" work around
fails
> if
> > > the
> > > > code is interrupted between the selection and test and it may
> interfere
> > > with
> > > > code working with multiple worksheets.
> > > >
> > > > The proper work around is find the color of the color index of the
> > color.
> > > >
> > > > To find the Interior color of cell "A1" use:
> > > > With ActiveSheet.Range("A1").Interior
> > > > If .ColorIndex < 0 Then
> > > > lngcolor = .Color
> > > > Else
> > > > lngcolor = ActiveWorkbook.Colors(.ColorIndex)
> > > > End If
> > > > End With
> > > >
> > > > To find the Font color used in cell "A1" use:
> > > > 'Warning: This code assumes all characters within the cell use the
> same
> > > font
> > > > color!
> > > > With ActiveSheet.Range("A1").Font
> > > > If .ColorIndex < 0 Then
> > > > lngcolor = .Color
> > > > Else
> > > > lngcolor = ActiveWorkbook.Colors(.ColorIndex)
> > > > End If
> > > > End With
> > > >
> > > >
> > > >
> > > > Some code examples use the .Color directly and others that use
> > .ColorIndex
> > > > directly. Both have problems.
> > > >
> > > > ColorIndex is fine as long as it is not the Color that is important
> but
> > > the
> > > > ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is
> one
> > > > example.
> > > >
> > > > Color is fine as long as the ColorIndex has not been modified prior
to
> > the
> > > > current execution.
> > > >
> > > > Thanks for hashing this out with me.
> > > >
> > > >
> > > > PS. I normally define and set worksheet and workbook objects then
use
> > > those
> > > > in my code. This was not tested and may have some impact, although
> I"m
> > > sure
> > > > the final solution will work without problems.
> > > >
> > > >
> > > > --
> > > > My handle should tell you enough about me. I am not an MVP, expert,
> > guru,
> > > > etc. but I do like to help.
> > > >
> > > >
> > > > "Peter T" <peter_t@discussions> wrote in message
> > > > news:[email protected]...
> > > > > If I interpret what you say correctly everything returns as
expected
> > > when
> > > > > you run the code normally. So I don't see the need to "workaround"
> by
> > > > > selecting the activesheet which is not something to do for no good
> > > reason.
> > > > >
> > > > > In normal use you can reliably return .font.color. Or in both
normal
> &
> > > > debug
> > > > > mode
> > > > >
> > > > > idx = cell.font.colorindex
> > > > >
> > > > > if idx > 0 then
> > > > > colorvalue = cell.parent.parent.colors(idx) ' ie cell's
> > > > Workbook.Colors(idx)
> > > > > ' else
> > > > > ' automatic/system black, most typically colorvalue = 0
> > > > > end if
> > > > >
> > > > > Regards,
> > > > > Peter T
> > > > >
> > > > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in
message
> > > > > news:[email protected]...
> > > > > > I found a reference and consolidated it into:
> > > > > > First row from left to right: 1, 53, 52, 51, 49, 11, 55, 56
> > > > > > second row: 9, 46, 12, 10, 14, 5, 47, 16
> > > > > > third row: 3, 45, 43, 50, 42, 41, 13, 48
> > > > > > fourth row: 7, 44, 6, 4, 8, 33, 54, 15
> > > > > > fifth row: 38, 40, 36, 35, 34, 37, 39, 2
> > > > > > sixth row first default row for charts: 17, 18, 19, 20, 21, 22,
> 23,
> > > 24
> > > > > > seventh row. second default row for charts: 25, 26, 27, 28, 29,
> 30,
> > > 31,
> > > > > 32
> > > > > >
> > > > > > [a6] was set to Index 6, which is the 4th row, 3rd column, which
> is
> > > > > yellow.
> > > > > > Thus, you have shown the similar pattern of a change in values
> based
> > > on
> > > > > > executing code and code executed after a break. Seemingly
> > regardless
> > > of
> > > > > > whether the break was a break point or an End statement.
> > > > > >
> > > > > > I modified your code slightly.
> > > > > > ' Workbooks.Add
> > > > > > ActiveWorkbook.ResetColors
> > > > > >
> > > > > > 'x = r.Font.Color
> > > > > > Debug.Print ActiveSheet.Range("A6").Font.Color
> > > > > > Debug.Print ActiveSheet.Range("A6").Font.Color '<== Add
> > > > Breakpoint
> > > > > > ActiveSheet.Select
> > > > > > Debug.Print ActiveSheet.Range("A6").Font.Color
> > > > > >
> > > > > >
> > > > > > The results are the same, of course. If the code is run
non-stop
> > then
> > > > all
> > > > > > three Debug.Prints display the value 255. If a breakpoint is
> placed
> > > on
> > > > > the
> > > > > > second Debug.Print statement then the first returns 255, the
> second
> > > > 65535,
> > > > > > and the third 255.
> > > > > >
> > > > > >
> > > > > >
> > > > > > So, it does seem that adding an Activesheet.Select statment
> provides
> > a
> > > > > > possible workaround. Perhaps such a silly line of code should
be
> > > > > documented
> > > > > > to prevent a rational person from removing such a ridiculous.
The
> > > > comment
> > > > > > needs to state that setting a breakpoint will give different
> results
> > > and
> > > > > > explain why. Sure wish I knew why.
> > > > > >
> > > > > >
> > > > > >
> > > > > > At least we found what Mr. Bean did prior to becoming a
comedian.
> > > > > >
> > > > > >
> > > > > > --
> > > > > > My handle should tell you enough about me. I am not an MVP,
> expert,
> > > > guru,
> > > > > > etc. but I do like to help.
> > > > > >
> > > > > >
> > > > > > "Peter T" <peter_t@discussions> wrote in message
> > > > > > news:[email protected]...
> > > > > > > I get similar results but only when I start by stepping
through
> > with
> > > > F8
> > > > > > >
> > > > > > > Sub test()
> > > > > > >
> > > > > > > ' compare difference in debug results between
> > > > > > > ' step through with F8 & run with F5
> > > > > > >
> > > > > > > Workbooks.Add
> > > > > > >
> > > > > > > For i = 1 To 16
> > > > > > > Cells(i, 1).Font.ColorIndex = i
> > > > > > > Next
> > > > > > >
> > > > > > > For i = 1 To 16
> > > > > > > ActiveWorkbook.Colors(i) = 255
> > > > > > > Next
> > > > > > >
> > > > > > > For i = 1 To 16
> > > > > > > ' should return : i 255 255
> > > > > > > ' though stepping through returns: i default palette
color(i)
> > > 255
> > > > > > >
> > > > > > > Debug.Print i, Cells(i, 1).Font.Color, _
> > > > > > > ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex)
> > > > > > > Next
> > > > > > >
> > > > > > > Dim r As Range
> > > > > > > Set r = [a6]
> > > > > > > 'put a break on next line and put cursor over r.Font.Color
> > > > > > > x = r.Font.Color
> > > > > > > ' tip under cursor reads = 65536 (yellow) but in Locals r.font
=
> > 255
> > > > as
> > > > > > > expected
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > > I've spent considerable time working with the Excel palette
and
> > > still
> > > > > > don't
> > > > > > > fully understand it's deep inner workings. Appears to belong
to
> > the
> > > > > > > workbook's "windows" object, which itself is a rather odd
thing.
> > But
> > > > > where
> > > > > > > or how is the "default" palette stored & defined. Doing
certain
> > > things
> > > > > > with
> > > > > > > the palette can crash Excel (albeit in rare scenarios).
> > > > > > >
> > > > > > >
> > > > > > > > I don't see how a dual palette can be used since changing
the
> > > color
> > > > of
> > > > > > an
> > > > > > > > index immediately changes the color throughout the workbook.
> > > > > > >
> > > > > > > One of those can't-be-possible-but-is things. With two
windows,
> > > apply
> > > > > > > colours to cells in one window and see different colours from
> the
> > > > > "other"
> > > > > > > palette update in same cells in the other window. Switch
windows
> > and
> > > > the
> > > > > > > drop down palette changes.
> > > > > > >
> > > > > > > Regards,
> > > > > > > Peter T
> > > > > > >
> > > > > > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in
> > message
> > > > > > > news:[email protected]...
> > > > > > > > I don't see how a dual palette can be used since changing
the
> > > color
> > > > of
> > > > > > an
> > > > > > > > index immediately changes the color throughout the workbook.
> > But,
> > > > > there
> > > > > > > is
> > > > > > > > a lot I don't know and can't imagine!
> > > > > > > >
> > > > > > > > Now, this has become even more confusing the code below
(much
> of
> > > it
> > > > is
> > > > > > > > redundant) was executed in two ways, each with similar but
> > > different
> > > > > > > > results.
> > > > > > > >
> > > > > > > > First it was executed in the Immediate Window; one step at a
> > time.
> > > > > Both
> > > > > > > > ActiveWorkbook and ThisWorkbook were used. No change since
> the
> > > code
> > > > > > > > effectively resides in the ActiveWorkbook. The results
were:
> > > > > > > > 16711680
> > > > > > > > 32
> > > > > > > > 255
> > > > > > > > 255
> > > > > > > > 255
> > > > > > > > (ActiveSheet.Select performed here)
> > > > > > > > 16711680
> > > > > > > > 16711680
> > > > > > > > Color Test
> > > > > > > >
> > > > > > > > Then a new workbook was manually created, a module inserted,
> and
> > > the
> > > > > > code
> > > > > > > > was copied to a subroutine. The Workbooks.Add code was
> > commented
> > > > out.
> > > > > > > > Before executing a second subroutine was created consisting
of
> > > three
> > > > > > lines
> > > > > > > > of code
> > > > > > > > Debug.Print ActiveSheet.Range("A1").Font.Color
> > > > > > > > ActiveSheet.Select
> > > > > > > > Debug.Print ActiveSheet.Range("A1").Font.Color
> > > > > > > >
> > > > > > > > These lines of code exist within the first subroutine so I
> would
> > > > think
> > > > > > the
> > > > > > > > results would be the same.
> > > > > > > >
> > > > > > > >
> > > > > > > > The results were:
> > > > > > > >
> > > > > > > > First Routine, notice that the Font Color was reported as
255
> > > (red)
> > > > > five
> > > > > > > > times in a row, unlike in the Immediate Window.
> > > > > > > > 16711680
> > > > > > > > 255
> > > > > > > > 32
> > > > > > > > 255
> > > > > > > > 255
> > > > > > > > 255
> > > > > > > > (ActiveSheet.Select performed here)
> > > > > > > > 255
> > > > > > > > 255
> > > > > > > > Color Test
> > > > > > > >
> > > > > > > > Second Routine, oops.
> > > > > > > > 16711680
> > > > > > > > 255
> > > > > > > >
> > > > > > > > (I'm sending the rest of my hair to Microsoft, but if they
are
> > not
> > > > > > careful
> > > > > > > I
> > > > > > > > will send them my first born later.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > '=================== Start of code
> > > > > > > > 'Start a new workbook being sure to start with the default
> > colors
> > > > and
> > > > > a
> > > > > > > > known worksheet.
> > > > > > > > Workbooks.Add Template:="Workbook"
> > > > > > > > ActiveWorkbook.ResetColors
> > > > > > > > Activeworkbook.Sheets(1).select
> > > > > > > > Activeworkbook.Sheets(1).activate
> > > > > > > > Activeworkbook.Sheets(1).Name = "Color Test"
> > > > > > > >
> > > > > > > > 'place the word Color in A1
> > > > > > > > ActiveSheet.Range("A1").value = "Color"
> > > > > > > >
> > > > > > > > 'Make the text of a size that color can be more readily
seen.
> > > > > > > > Activesheet.Range("A1").Font.Size = 14
> > > > > > > > Activesheet.Range("A1").Font.Bold = True
> > > > > > > >
> > > > > > > > 'Change the font color of A1 to the color in index 32. The
> font
> > > is
> > > > > now
> > > > > > > > blue.
> > > > > > > > Activesheet.Range("A1").Font.ColorIndex = 32
> > > > > > > >
> > > > > > > > 'Report the Font Color in the cell (FF0000, or
16711680;
> ie
> > > > Blue)
> > > > > > > > debug.Print Activesheet.Range("A1").Font.Color
> > > > > > > >
> > > > > > > > 'change the color of Index 32 to Red. The font is now
red.
> > > > > > > > ActiveWorkbook.colors(32) = &HFF
> > > > > > > >
> > > > > > > > 'Report the ColorIndex value. (32)
> > > > > > > > debug.Print Activesheet.Range("A1").Font.ColorIndex
> > > > > > > >
> > > > > > > > 'Report the Color of the ColorIndex 32 (FF, ie Red)
> > > > > > > > debug.Print ActiveWorkbook.Colors(32)
> > > > > > > >
> > > > > > > > 'Report the Font Color in the cell (FF, or 255; ie
> Red....
> > > > huh?)
> > > > > > > > debug.Print Activesheet.Range("A1").Font.Color
> > > > > > > >
> > > > > > > > 'Report the Font Color in the cell again (FF, or 255; )
> > > > > > > > debug.Print Thisworkbook.WorkSheets("Color
> > > > > Test").Range("A1").Font.Color
> > > > > > > >
> > > > > > > > 'Now, select the ActiveSheet, just for fun
> > > > > > > > Activesheet.select
> > > > > > > >
> > > > > > > > 'Report the Font Color in the cell again (FF0000, or
> > > > 16711680; )
> > > > > > > > debug.Print Activesheet.Range("A1").Font.Color
> > > > > > > >
> > > > > > > > 'Ok, what is the font color now?
> > > > > > > > debug.Print Thisworkbook.WorkSheets("Color
> > > > > Test").Range("A1").Font.Color
> > > > > > > >
> > > > > > > > 'Do a bit of testing
> > > > > > > > debug.Print Thisworkbook.ActiveSheet.name
> > > > > > > > '=================== End of code
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > > My handle should tell you enough about me. I am not an MVP,
> > > expert,
> > > > > > guru,
> > > > > > > > etc. but I do like to help.
> > > > > > > >
> > > > > > > >
> > > > > > > > "Peter T" <peter_t@discussions> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > > So what's the actual code you use.
> > > > > > > > >
> > > > > > > > > #32 in a default palette is 100% blue.
> > > > > > > > >
> > > > > > > > > It is possible to create a workbook that sustains two
unique
> > > > > palettes
> > > > > > > > > concurrently, one default and one customized, each
viewable
> in
> > > > > > different
> > > > > > > > > windows of the same workbook. There's a bit of a knack to
> > doing
> > > > this
> > > > > > (I
> > > > > > > > > always forget!) and easy to loose the dual palette.
Perhaps
> > > > > something
> > > > > > > > along
> > > > > > > > > these lines is occurring for you.
> > > > > > > > >
> > > > > > > > > Regards,
> > > > > > > > > Peter T
> > > > > > > > >
> > > > > > > > > "AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote
in
> > > > message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > No, but good point.
> > > > > > > > > >
> > > > > > > > > > In this case the code resides in the current workbook so
> > > > > > > ActiveWorkbook
> > > > > > > > > and
> > > > > > > > > > ThisWorkbook are the same.
> > > > > > > > > >
> > > > > > > > > > Thanks for the thought.
> > > > > > > > > >
> > > > > > > > > > A co-worker said he had a similar situation with Excel
> > > workbooks
> > > > > > > created
> > > > > > > > > by
> > > > > > > > > > Business Objects. Opening the workbook allowed viewing
> the
> > > > report
> > > > > > but
> > > > > > > > > > trying to print or do a print preview generated an
error.
> > > > > Clicking
> > > > > > on
> > > > > > > > the
> > > > > > > > > > worksheet tab "fixed" the problem. Essentially they are
> > > > maunally
> > > > > > > doing
> > > > > > > > an
> > > > > > > > > > Activeworksheet.Select to work around the issue.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Actually I'm "glad" you persevered with the belief of your perceptions,
despite any hint I doubted (not intended) !

I think this deserves the status of "bug", albeit a mild one with an easy
workaround. I hadn't noticed it before because I only return palette colours
in objects that cannot accept an RGB format.

I'm also surprised never to have come across mention of it in this ng or
elsewhere, which is not to say it isn't documented.

I don't know of any "effective" means to report bugs. I've tried in the past
with much more serious issues regarding the palette & VBA.

Regards,
Peter T


"AnExpertNovice" <j@The~N_o~S_p_a_m~PostOffice.com> wrote in message
news:#[email protected]...
> Well, I'm "glad" you could confirm this feature on your system. The
problem
> exists, as one would assume, in both Excel 2000 under Win 2K and 2002
under
> Win XP.
>
> Your tests were interesting because you were able to replicate with code
> what I was producing with manual actions.
>
> As for the conclusion
> > Or apply some format (even same format), but as Font can have mixed
> formats
> > best to apply to .Interior.
>
> This still breaks when code execution is stopped (debug.assert, stop,
break
> point, and other errors) so determining the color of the color index
appears
> to be the only decent solution.
>
> Take care. Thanks for the conversation.
>
> --
> My handle should tell you enough about me. I am not an MVP, expert, guru,
> etc. but I do like to help.
>
<snip>