Macro to format row if a certain value is enetered into a cell

[Reneek]

hi there

I am after a macro that will run automatically when I enter the value of "NV" (abbreviation for "no longer valid") into the column W.

I would like it to then highlight that row and format the text with a strikethrough and change the font colour to dark grey.

The value of NV is selected by a drop down box.

Can anyone help?

Thanks in advance.

 

[Yahya]

Right click on the sheet name --> view code
then paste this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 23 Then Exit Sub
If Target.Value = "NV" Then
    With Range(Cells(Target.Row, 1), Cells(Target.Row, 23))
        .Select
        .Font.Strikethrough = True
        .Interior.ColorIndex = 7 'change the color index as you want
    End With
End If
End Sub

 

[Smitty]

Welcome to the Board!

Why use VBA when you can do all of that with Conditional Formatting? Plus you won't have to add to the code in case you want to change NV to something else.

HTH,

 

[Reneek]

Thank you Yahya - works perfectly - thanks for the quick response!

Cheers
R

 

[Reneek]

Thank You Smitty - the conditional Formatting also works
Cheers
R

 

[Yahya]

Thank you Yahya - works perfectly - thanks for the quick response!

Cheers
R

you are welcome