Formatting Font in a range based on reference range

[Patrick3001]

Hello all,

I'm sure this is very simple but I'm stuck.

I have a range of cells - L1:L1000 all of which need to have their font changed if the respective cell in T1:T1000 = "Caution".

I've got conditional formatting for background shading, but thats no good for people using black and white printers hence the font change.

So far I'm at:

Private Sub Worksheet_Calculate()
If (Range("T1")) = "Caution" Then
Range("L1").Font.Name = "Impact"
Else
Range("L1").Font.Name = "Calibri"
End If


This seems to work fine for "L1", I just don't know how to make it work through each row up to L1000.

I'm using excel 2010, but file has to also run in 2003.

Any help would be greatly appreciated.

Thanks

 

[Jon von der Heyden]

Welcome to the board!

Try;

Private Sub Worksheet_Calculate()
    Dim rngCell As Range
    
    For Each rngCell In Range("L1:L1000")
        If rngCell.Text = "Caution" Then
            rngCell.Font.Name = "Impact"
        Else
            rngCell.Font.Name = "Calibri"
        End If
    Next rngCell
End Sub

 

[JackDanIce]

n/a

 

[VoG]

Try

Private Sub Worksheet_Calculate()
Dim i As Long
For i = 1 To 1000
    If Range("T" & i).Value = "Caution" Then
        Range("L" & i).Font.Name = "Impact"
    Else
        Range("L" & i).Font.Name = "Calibri"
    End If
Next i
End Sub

 

[WinteE]

Try this :

Sub TestFont()

    For Each c In Range("l1:l30")
        If Range("T" & c.Row) = "Caution" Then
            c.Font.Name = "Impact"
        Else
            c.Font.Name = "Calibri"
        End If
    Next c

End Sub

Success
Erik

 

[Patrick3001]

Erik, Peter,

Thank you both so much - I've checked, and both of your responses work exactly as requested.

Thank you again.


Patrick