Formatting desired cells

**rupeshkumar_rj** · 02-11-2007, 10:04 AM

Used the below code for formatting the desired cells on changing the content of a particular cell. This worked fine only when I clicked on the cell after changing the cell content. I have used a list box to change the value of that particular cell. Trying to do a simple thing ...
-When a cell's (say A2) value is changing from "one" to "two", the font color of the adjacent cell/any other cell (say B2) should change from red to green.

anybody ? who can help

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim i As Integer
Dim cel As Range
Set cel = ActiveCell
 
    If cel.Row > 2 And cel.Column = 9 Then
        If Range("I" & cel.Row) = "Major" Then
            Range("A" & cel.Row & ":I" & cel.Row & "").Font.Color = vbRed
        ElseIf Range("I" & cel.Row) = "Minor" Then
            Range("A" & cel.Row & ":I" & cel.Row & "").Font.Color = vbBlue
        ElseIf Range("I" & cel.Row) = "NIL" Then
            Range("A" & cel.Row & ":I" & cel.Row & "").Font.Color = vbGreen
        End If
    End If
End Sub

**jasoncw** · 02-11-2007, 11:09 AM

I have some suggestions.

1. Rather than using the selection change event, I would use the change event.

2. Change all 'cel' references to 'Target'.

3. I also included the LCase function so that the case doesn't have to match (i.e. "major", "Major", and "MAJOR" will all accomplish the same).

Here is the revised code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Row > 2 And Target.Column = 9 Then
        If LCase(Range("I" & Target.Row)) = "major" Then
            Range("A" & Target.Row & ":I" & Target.Row & "").Font.Color = vbRed
        ElseIf LCase(Range("I" & Target.Row)) = "minor" Then
            Range("A" & Target.Row & ":I" & Target.Row & "").Font.Color = vbBlue
        ElseIf LCase(Range("I" & Target.Row)) = "nil" Then
            Range("A" & Target.Row & ":I" & Target.Row & "").Font.Color = vbGreen
        End If
    End If
End Sub