Any change in cell should show error

[pksinghal]

Hi,

I have following list of names.

S.No.	Name
1	RKG
2	DG
3	PB
4	AP
5	GS
6	PS
7	PURRS
8	GH
9	AM

<colgroup><col><col></colgroup><tbody>
</tbody>

If value in cells "Name" is changed....Cell Text should become RED.

Like if someone RKG is replaced by MKG then MKG should be in RED

Please help

 

[Tim_Excel_]

Paste this in the correct worksheet module:

Sub Worksheet_Change(ByVal Target as Range)

Target.Font.ColorIndex = 3

End Sub

This changes the font colour of any cell that is changed. If you need it to work for only a certain range, add this line on top of the code:

If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub    'change range accordingly

 

[pksinghal]

This is not giving desired result...change of Color should happens only value other than specified value is entered.

Paste this in the correct worksheet module:

Sub Worksheet_Change(ByVal Target as Range)

Target.Font.ColorIndex = 3

End Sub

This changes the font colour of any cell that is changed. If you need it to work for only a certain range, add this line on top of the code:

If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub    'change range accordingly

 

[Tim_Excel_]

I don't see what you are trying to say. According to your first post, you want the text colour of the specific cell that has been changed in column "Name" to be red, which is what I did.
What do you mean with "only value other than specified value is entered"?

 

[Peter_SSs]

.. cell that has been changed in column "Name" to be red, which is what I did.

Not quite, and perhaps this is what the OP is getting at. If cell B2 contains RKG and RKG is entered in that cell again, your code turns it red, yet the value was not "changed" in the English sense of the word. Just a guess.

 

[Jaafar Tribak]

If I understand you correctly, you may want to use CF for this via the use of helper list in a seperate range.

The following assumes that the names cells are down the range : ("B2:B10")
The helper list is in range : ("G2:G10") .. You can hide this helper column if you want.

Change the above addresses as required :

Selet the B2:B10 range and go to CF .

The Conditional Formatting formula would be : =$B2:$B10<>$G1:$G9 and set the Font color to RED

Alternatively, if you want to use VBA then this may help :

Code in the worksheet module :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ar() As Variant
    Dim i As Integer
    
    ar = Array("RKG", "DG", "PB", "AP", "GS", "PS", "PURRS", "GH", "AM")
    
    If Not Intersect(Target, Me.Range("B2:B10")) Is Nothing Then
        With Me.Range("B2:B10")
            For i = 1 To UBound(ar)
                If .Cells(i) <> ar(i - 1) Then
                    .Cells(i).Font.Color = vbRed
                Else
                    .Cells(i).Font.Color = vbBlack [B][COLOR=#006400]'<== Or any other preset color.[/COLOR][/B]
                End If
            Next i
        End With
    End If
End Sub

 

[Jaafar Tribak]

The above code should read :

For i = 1 To UBound(ar) + 1