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
------------------------------------------------------------------------
BrianDP1977's Profile: http://www.excelforum.com/member.php...o&userid=29110
View this thread: http://www.excelforum.com/showthread...hreadid=490642

Well, since I can't seem to get a complete answer, I'll try breaking i
up a bit. I guess my biggest question is how do I make the compariso
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 no
being able to reference the font for other stuff later on) I'd d
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

--
BrianDP197
-----------------------------------------------------------------------
BrianDP1977's Profile: http://www.excelforum.com/member.php...fo&userid=2911
View this thread: http://www.excelforum.com/showthread.php?threadid=49064

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

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

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

(“DP6_Name” Range(cells A1:A3)) (“DP6_Date_Start” Range(cell
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 ar
any matches in the Alt_Name range. If so, the font color for the cel
containing the name in the DP6_Name range should be orange and stop an
further testing (for the above example, Brad’s name would get shade
orange and would not require any further searches in the next conditio
… I planned on doing this with a Skip command that would take it to th
end Sub).

If the name in the cell is not in Alt_Name, continue with the followin
conditions. Check each respective start date month. If this month i
DEC, JAN, or FEB make the font color for the displayed name Red. I
this month is MAR, APR, or MAY make the font color for the displaye
name green. If this month is JUN, JUL, or AUG make the font color fo
the displayed name blue. If this month is SEP, OCT, or NOV make th
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 coul
would work for the date stuff:


Code
-------------------
Sub Name_font_color()
Dim c As Range

For Each c In Range("DP6_Date_Start")

If Month(c.Value) = 12 Or Month(c.Value) = 1 Or Month(c.Value) = 2 Then
Range("DP6_Names").Row(c).Font.ColorIndex = 3
End If

If Month(c.Value) = 3 Or Month(c.Value) = 4 Or Month(c.Value) = 5 Then
Range("DP6_Names").Row(c).Font.ColorIndex = 50
End If

If Month(c.Value) = 6 Or Month(c.Value) = 7 Or Month(c.Value) = 8 Then
Range("DP6_Names").Row(c).Font.ColorIndex = 5
End If

If Month(c.Value) = 9 Or Month(c.Value) = 10 Or Month(c.Value) = 11 Then
Range("DP6_Names").Row(c).Font.ColorIndex = 0
End If

End Su
-------------------


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

--
BrianDP197
-----------------------------------------------------------------------
BrianDP1977's Profile: http://www.excelforum.com/member.php...fo&userid=2911
View this thread: http://www.excelforum.com/showthread.php?threadid=49064

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


Code:
--------------------
For Each c In Range("DP6_Date_Start")
Select Case Month(c.Value)
Case 1, 2, 12
c.Offset(0, -6).Font.ColorIndex = 3
Case 3, 4, 5
c.Offset(0, -6).Font.ColorIndex = 50
Case 6, 7, 8
c.Offset(0, -6).Font.ColorIndex = 5
Case 9, 10, 11
c.Offset(0, -6).Font.ColorIndex = 0
End Select
Next c
--------------------


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


--
BrianDP1977
------------------------------------------------------------------------
BrianDP1977's Profile: http://www.excelforum.com/member.php...o&userid=29110
View this thread: http://www.excelforum.com/showthread...hreadid=490642

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


Code:
--------------------
Sub Name_font_color()
Dim c As Range, cA As Range

For Each c In Range("DP6_Date_Start")
Select Case Month(c.Value)
Case 1, 2, 12
c.Offset(0, -6).Font.ColorIndex = 3
Case 3, 4, 5
c.Offset(0, -6).Font.ColorIndex = 50
Case 6, 7, 8
c.Offset(0, -6).Font.ColorIndex = 5
Case 9, 10, 11
c.Offset(0, -6).Font.ColorIndex = 0
End Select
Next c

For Each c In Range("DP6_Name")
For Each cA In Range("Alt_Name")
If c.Value = cA.Value Then
c.Font.ColorIndex = 45
End If
Next cA
Next c

End Sub
--------------------


--
BrianDP1977
------------------------------------------------------------------------
BrianDP1977's Profile: http://www.excelforum.com/member.php...o&userid=29110
View this thread: http://www.excelforum.com/showthread...hreadid=490642