excel vba generate format cells with different font color

I want to generate an excel sheet in a certain layout with vba. One of my subroutines is for font color. It looks like this:

Sub SetFont(cell1, cell2 As range, fcolor As String)

range(cell1, cell2).Sel开发者_如何学JAVAect
If fcolor = "w" Then
    With Selection.Font
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With
ElseIf fcolor = "b" Then
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
End If

End Sub

But it does not work. The font is always generated in black. I don't know why.

It works for me, however there is a reversal; xlThemeColorLight1 produces dark text and xlThemeColorDark1 creates lighter text, which is pretty dumb but there you go.

Sub foo()
SetFont Range("A1"), Range("A6"), "b"
End Sub

Sub SetFont(cell1 As Range, cell2 As Range, fcolor As String)
If fcolor = "w" Then
    With Range(cell1, cell2).Font
        .ThemeColor = xlThemeColorLight1
    End With
ElseIf fcolor = "b" Then
    With Range(cell1, cell2).Font
        .ThemeColor = xlThemeColorDark1
    End With
End If
End Sub

Unrelated, but if you have cell1, cell2 As range only cell2 is declared of type range, cell1 remains a variant.

May be try this : Sub SetFont(cell1, cell2 As range, fcolor As String) range(cell1, cell2).Select If fcolor = "w" Then With Selection.Interior .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 End With ElseIf fcolor = "b" Then With Selection.Interior .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 End With End If

End Sub