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
...
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" wrote in message
...
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
...
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" wrote in message
...
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 we
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 we
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
...
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" wrote in
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.