Thread: VB Font Color based on Letter

Hello All-

I am new and trying to figure out visual basics. I have looked high and low on the net and cannot find what I am looking for. I am working with Excel 2007 but have to make it compatible with Excel 2003.

My situation: I have a workbook for scheduling. The first sheet has the dates for the entire year in Column A and the employee names in Row 2. The next 13 tabs are 28 day increments of time. I would like to enter all of the data on the first tab and have it automatically feed to the other sheets. For example, if I type in "V" for vacation, I want it to appear as Arial Narrow, 10 pt. font and bold. I have approximatly 10 conditions I need to add. Also, the background will remain white in all conditions.

Thank you!
aswb

Sorry I was not clear.

What I am trying to do is enter all information into the first worksheet and have the same information feed to other worksheets within the same workbook. Please pardon my lack of knowledge on how to paste snapshots of what I am doing.

Worksheet #1
REFRESH BUTTON

Code:

	JILL	MARY	BOB	JOE	PHIL
1-Jan	X	SL	T	X	X
2-Jan	X	X	T	X	X
3-Jan	X	X	X	X	V
4-Jan		X			V
5-Jan	H	H	H	H	H
6-Jan	CF		X		
7-Jan			X		
8-Jan	X	SL		X	T
9-Jan	X	PL	X	X	T
10-Jan	X	X	X	X	
11-Jan		X	A	X	X
12-Jan			X		X

X - Days Off (black) SL - Sick Leave (pink) V - Vacation (blue) T - Training (orange)
PL - Personal Leave (green) CF - Conference (aqua)
H - Holiday (red) A-Authorized Leave (purple)

***(desired font colors)


Worksheet #2 (Information from #1)

1-Jan 2-Jan 3-Jan 4-Jan 5-Jan 6-Jan 7-Jan

JILL
MARY
BOB
JOE
PHIL



I would like to have the letters be the colors listed above in the entire workbook without having to select each sheet and individually changing each letter for 50 employees for the entire year. I am not sure how to get started.

Does this make sense? I hope so, I am getting quite frustrated as I have been working on this all weekend and am still far from being done.

Thanks again,
aswb

As a side note: The only other visual basic code I have worked with was already written and I just added the color for the background based on the typed in number in a range of cells. I was able to figure that much out.

This will process the activesheet.
To make it more dynamic I added a legend table to the sheet. This named range can be expanded to add more codes.
The font colorindex is picked up from this table.

Code:

Sub Test()

    Dim shtTemp As Worksheet
    Dim rngLegend As Range
    Dim rngData As Range
    Dim rngCell As Range
    Dim lngIndex As Long
    
    Set rngLegend = ThisWorkbook.Names("Legend").RefersToRange
    
    Set shtTemp = ActiveSheet
    Set rngData = shtTemp.Range("A1").CurrentRegion
    For Each rngCell In rngData.Offset(1, 1).Resize(rngData.Rows.Count - 1, rngData.Columns.Count - 1).Cells
        If Len(rngCell.Value) > 0 Then
            lngIndex = Application.WorksheetFunction.Match(rngCell.Value, rngLegend, 0)
            If lngIndex > 0 Then
                rngCell.Font.ColorIndex = rngLegend.Cells(lngIndex).Font.ColorIndex
            End If
        End If
    Next
    
End Sub