Use conditional formatting in visual basic as rules keep breaking

Question

We have a table with conditional formatting and every time we complete a task we fill the cell with a Y for yes which turns the task row green to show its completed.

For example if E3 contains "Y" i want B3:D3 to turn green

However our spreadsheet is used by several different people and for some reason these conditioned rules keep breaking and they are a pain to keep entering for every row.

Is there a way to have these rules in Visual Basic so it doesn't keep breaking?

Thanks

Name	Task	Amount	Completed
			Y

Answer 1

Here's a sample VBA code that you can use for the cell change event in Excel:

===================================

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range

Set KeyCells = Range("E:E")

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

    If Target.Value = "Y" Then

        Range("B" & Target.Row & ":D" & Target.Row).Interior.Color = RGB(0, 255, 0)

    Else

        Range("B" & Target.Row & ":D" & Target.Row).Interior.ColorIndex = xlNone

    End If

End If

End Sub

===================================

This code will check if any cell in column E has been changed. If the value of the changed cell is "Y", it will change the background color of cells in columns B to D to green. If the value is not "Y", it will remove the background color.

To use this code, you need to open the VBA editor in Excel, double click the sheet where you want to apply the code. Then, paste the code into the editor and save the workbook as a macro-enabled file.