PDA

View Full Version : Solved: Defining font colors in range



BrianDP1977
12-04-2005, 10:48 PM
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
12-05-2005, 11:31 AM
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:


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

austenr
12-05-2005, 01:34 PM
Keeping it simple, would something like this work for you. In this example, "Month" is the range and is located in B1:B3. This is the test I think you are trying to accomplish.

Sub testmonth()
Dim mymonth As Variant
For Each mymonth In Range("Month")
If mymonth = "1" Then
mymonth.Font.ColorIndex = 3
End If
If mymonth = "2" Then
mymonth.Font.ColorIndex = 3
End If
If mymonth = "12" Then
mymonth.Font.ColorIndex = 3
End If
Next mymonth
End Sub

malik641
12-05-2005, 02:03 PM
(Quoted from ATHF: Episode = Frat Aliens)
"DP wassUP!!!!!!!!"

LOL, sorry. That's the first thing I thought of when I saw the named range...don't mean to get off topic, but I'm hoping they're some ATHF fans.


...but seriously,
I need a little help with the named ranges piece of the question.

Is there just ONE named range "DP6_Names" and the cells within that named range hold integers for dates? Or do the cells in the named range hold an actual date and you want the month from that data?

OR

Are there multiple names holding the month value of the name...example:
Month 1 Name = DP6_1
Month 2 Name = DP6_2

And do you want all Cells in those ranges to be a certain font color based on the name???


Just curious before I suggest anything :)

mdmackillop
12-05-2005, 02:26 PM
Hi Brian
Welcome to VBAX.
If you can zip and post a sample spreadsheet (use Manage Attachments in the Go Advanced section) this makes life easier as we can see exactly what your layout etc. is. Delete any sensitive data before posting.
Regards
MD

BrianDP1977
12-05-2005, 07:47 PM
With some help, I managed to solve the problem. Here is the final code which works pretty well:


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


Thanks to everyone for the help.

malik641
12-05-2005, 09:09 PM
Hey Brian, and welcome to the forums :hi: (didn't realize the first time I posted you were new :doh: )

Anyway here's a little something to help clean it up :thumb


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

And if the thread is solved be sure to mark it so by choosing "Mark Thread Solved" in the forum tools of this thread :yes

BrianDP1977
12-05-2005, 10:03 PM
Thank you very much. I'll be sure to change the code. Thanks.