Idenitfying Font Color

[Poody]

I'm trying to write a macro that updates a column for each task based on the
font color that is used. I haven't been able to figure out how to get the
font color value out of the FontColor property.

Can anyone help me out?

As an example, in Excel, I could use something like:

cellColor = Range("A1").Interior.ColorIndex

Is there something similar for MS Project? I'm using MS Project 2002.

Thanks in advance.

Rich

 

[Poody]

John,

Thanks for the information. I was afraid of that.

Thanks again,

Rich

:

 

[John]

I'm trying to write a macro that updates a column for each task based on the
font color that is used. I haven't been able to figure out how to get the
font color value out of the FontColor property.

Can anyone help me out?

As an example, in Excel, I could use something like:

cellColor = Range("A1").Interior.ColorIndex

Is there something similar for MS Project? I'm using MS Project 2002.

Thanks in advance.

Rich

Rich,
Unfortunately, unlike Excel, font properties in Project cannot be "read"
directly. Font properties can be determined but it requires a criteria
algorithm. Some criteria must have been used to set the font color in
the first place (e.g. summary line, tasks with a certain date, tasks
that are late, etc.). What you need to do then is to use the same
criteria to effectively "decode" the font color and then update the
column accordingly. So, unless the font colors were set in a complete
random fashion, you can do what you want but it takes a little work.

John
Project MVP

 

[JackD]

Using individual cell formatting as a way to distinguish specific tasks is a
poor practice to follow, even in excel.
You should just use another field (one of the flag fields or text fields or
number fields) to identify tasks.

 

[Poody]

It's not that I'm using individual cell formatting to distinguish specific
tasks. I'm supporting a group of individuals that all use different colors
to highlight different information on their Project Plans. When the plan is
exported to Excel the different colors are lost and I was hoping to find a
way to put a color indicator in a field that isn't used by this group (such
as Text30) via a macro (so the PMs don't have to update an additional field).

I have a macro that formats the Excel export to look more like Project and I
was wanting to modify that macro to port over the font colors that each
person uses.

I hope this helps to clarify.

Rich

 

[John]

It's not that I'm using individual cell formatting to distinguish specific
tasks. I'm supporting a group of individuals that all use different colors
to highlight different information on their Project Plans. When the plan is
exported to Excel the different colors are lost and I was hoping to find a
way to put a color indicator in a field that isn't used by this group (such
as Text30) via a macro (so the PMs don't have to update an additional field).

I have a macro that formats the Excel export to look more like Project and I
was wanting to modify that macro to port over the font colors that each
person uses.

I hope this helps to clarify.

Rich

Rich,
Great, now all you need to add to your macro is a set of criteria
algorithms to pull out the color information. It shouldn't be that
difficult. . . as long as each user is consistent with their color
scheme.

John

 

[Poody]

:

Rich,
Great, now all you need to add to your macro is a set of criteria
algorithms to pull out the color information. It shouldn't be that
difficult. . . as long as each user is consistent with their color
scheme.

John

Yeah - that's the problem, they don't. However, I may take this opportunity
to see if I can't get that implemented...

Rich

 

[John]

:


Yeah - that's the problem, they don't. However, I may take this opportunity
to see if I can't get that implemented...

Rich

Rich,
Good luck. Instilling discipline to foster consistency is probably the
hardest part of working with other people on a common playing field.
When I was a project manager at my company I taught our users the "dos"
and "don'ts" of working with Project within our company format, but
invariably one or more of the "trained" users didn't follow the rules.
VBA can do virtually anything when working with structured plans but it
ain't worth a darn when it comes to fuzzy logic.

John

 

[Poody]

Rich,
Good luck. Instilling discipline to foster consistency is probably the
hardest part of working with other people on a common playing field.
When I was a project manager at my company I taught our users the "dos"
and "don'ts" of working with Project within our company format, but
invariably one or more of the "trained" users didn't follow the rules.
VBA can do virtually anything when working with structured plans but it
ain't worth a darn when it comes to fuzzy logic.

Yeah - that is what I am finding as well... Several of them are starting to
see that limitation of VBA too - so that is helping a bit. I may have to
make a unilateral decision on the colors, though... Not my preference,
but it would get me the answers I need anyway.