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

[AnExpertNovice]

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?

 

[AnExpertNovice]

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

 

[Peter T]

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]

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.

 

[Peter T]

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]

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

 

[Peter T]

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]

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.

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

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.


on
the doing

 

[Peter T]

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

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.

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

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.


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

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

 

[AnExpertNovice]

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.

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

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.

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

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 this
(I report
but Clicking

 

[Peter T]

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

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.

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

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.


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

color

of

it

is (red)
five and is
now

 

[AnExpertNovice]

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.

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

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.

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

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.


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

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.


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

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.

 

[Peter T]

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

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.

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

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.


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

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.


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

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.


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

message
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.

 

[AnExpertNovice]

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.

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

The isnull is nice to know, thanks!

because the Font.color or interior.color can return either the default or
actual color.... depending.


/
step

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.


knows.
But to
the /
step

fails

if to
the

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

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.


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

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 are
not

16711680;

ie

viewable

in

 

[Peter T]

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

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

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.

<snip>