Font color question

[BrianDP1977]

My cell font color hopefully... but maybe not.

I have a range of names (DP6_Names). Equated with each name is a start date. All these dates are on the same row as their respective name and are defined as the named range DP6_Date_Start. There is another list of names (range Alt_Names) which may or may not contain the same name as one in the DP6_Names range of names. I need to define the font color used for the name in DP6_Names based on this logic:

If the name in DP6_Names is also a name found in Alt_Names, the DP6_Names font color for that specific name should be light orange. No other conditions should apply after this.

If the start date month for the respective DP6_Names name is either 12, 1, or 2 (i.e. DEC, JAN, FEB) the font color should be red.

If the start date month for the respective DP6_Names name is either 3, 4, or 5 the font color should be sea green.

If the start date month for the respective DP6_Names name is either 6, 7, or 8 the font color should be blue.

If the start date month for the respective DP6_Names name is either 9, 10, or 11 the font color should be black.

I kind of know how to do this with conditional formatting except there are two problems. First, I don’t have enough conditions. However, more importantly, I need to be able to copy the name cell values formatting on other sheets (i.e. the defined font color) and if I used conditional formatting the font color would not be able to be referenced directly from the cell. Thank you for the help.

[BrianDP1977]

Well, since I can't seem to get a complete answer, I'll try breaking it up a bit. I guess my biggest question is how do I make the comparison of each cell's month in the range(i.e. c.value = Month 3 or 4 or 5)?

With conditional formatting (which I do not want to use due to not being able to reference the font for other stuff later on) I'd do something like this:

Please Login or Register  to view this content.

However, I do not know how to format a similar If "month" check in VBA.

[David McRitchie]

IF month(c.value) >= 3 AND month(c.value) <= 5 then

IF month(c.value) = 3 OR month(c.value) = 4 OR month(c.value) = 5 then

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"BrianDP1977" <[email protected]> wrote in message
news:[email protected]...
>
> Well, since I can't seem to get a complete answer, I'll try breaking it
> up a bit. I guess my biggest question is how do I make the comparison
> of a cells month (i.e. c.value = Month 3 or 4 or 5)?
>
> With conditional formatting (which I do not want to use due to not
> being able to reference the font for other stuff later on) I'd do
> something like this:
>
>
> Code:
> --------------------
> =OR(MONTH(c.value)=3, MONTH(c.value)=4, MONTH(c.value)=5)
> --------------------
>
> However, I do not know how to format a similar If "month" check in VBA.
>
>
> --
> BrianDP1977
> ------------------------------------------------------------------------
> BrianDP1977's Profile: http://www.excelforum.com/member.php...o&userid=29110
> View this thread: http://www.excelforum.com/showthread...hreadid=490642
>

[BrianDP1977]

Thanks, that's a start. I think I’m doing a bad job of explaining what I need. Here’s an example:

(“DP6_Name” Range(cells A1:A3)) (“DP6_Date_Start” Range(cells C1:C3))
..........Tom ................................... 25 JAN 06
...........Jim .................................... 4 MAR 06
..........Brad .................................... 5 JUN 06


(“Alt_Name” Range (cells A10:A13))
........... Jill
........... Brad
........... Brian

First, I need to check the DP6_Name range column and see if there are any matches in the Alt_Name range. If so, the font color for the cell containing the name in the DP6_Name range should be orange and stop any further testing (for the above example, Brad’s name would get shaded orange and would not require any further searches in the next condition … I planned on doing this with a Skip command that would take it to the end Sub).

If the name in the cell is not in Alt_Name, continue with the following conditions. Check each respective start date month. If this month is DEC, JAN, or FEB make the font color for the displayed name Red. If this month is MAR, APR, or MAY make the font color for the displayed name green. If this month is JUN, JUL, or AUG make the font color for the displayed name blue. If this month is SEP, OCT, or NOV make the font color for the displayed name black. Using the above example, Tom’s name would be red and Jim’s would be green. I thought this could would work for the date stuff:

Please Login or Register  to view this content.

However, I'm obviously formatting something incorrectly. I'm trying to look at each value in the DP6_Date_Start range, determine which month it's in, and then reference its row placement and use this to define the color of the matching row in DP6_Name based on the condition it matched. I haven't even started working on the first condition yet (i.e. the alt name thing).

[BrianDP1977]

With some help, the following code does the trick for the date part.

Please Login or Register  to view this content.

Now I just need to figure out the DP6_Name vs Alt_Name part

[BrianDP1977]

For any interested, here is the completed code. Everything works well:

Please Login or Register  to view this content.