Formatting

[colinh]

Hi All

I have a work sheet and in any cell in this worksheet if I type the word "Yes" in any case upper or lower the cell highlights yellow and the word is bold.

I can do this with conditional formatting for specific cells but I dont know in what cell or cells I need to type the word yes so it needs to be in any cell in the worksheet

Thanks in advance

Colin

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
With Target
    If LCase(.Value) = "yes" Then
        .Interior.ColorIndex = 6
        .Font.Bold = True
        .Value = StrConv(.Value, vbProperCase)
    End If
End With
Application.EnableEvents = True
End Sub

 

[colinh]

Thanks very much can I ask if I clear the word it returns back to a std cell

Colin

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
With Target
    If LCase(.Value) = "yes" Then
        .Interior.ColorIndex = 6
        .Font.Bold = True
        .Value = StrConv(.Value, vbProperCase)
    ElseIf .Value = "" Then
        .Interior.ColorIndex = xlNone
        .Font.Bold = False
    End If
End With
Application.EnableEvents = True
End Sub

 

[Ingolf]

Hey
With conditional formatting you can click on left column A to select all sheet and put this formula =A1="yes"