Color Entirerow based on a column's value

---

Query Source   : Excel Macros Google Group
Solution Type   : Event Macro and Data Validation
Query by          : Avinash Daga
Solution by       : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query / Problem:

I have two buttons on column P which called active and deactivate.When I click active it should change color of  that row as green,means that project is active and once that is completed i can deactivate that by changing it to original text color. This is working for raw 1 but when I am dragging it down to make it work for all rows I am not able to do so.Please help me to have that thing changed for each row. 
Link to Problem File

---

 Solution:

Instead of using buttons a better solution is to include Data Validation (as you've used it in other columns) and Worksheet Event Macro (Worksheet_Change). Here is how you can do this:
Data Validation
1. Select Cell P2.
2. Go to Data --> Validation --> Settings.
3. Change Allow to List and Source to Active,Deactive. Click Ok.
4. Copy the cell P2 using Fill series/Fill Down or any other method you wish.

Use Worksheet Event Macro
Event macros should be placed only in the code window of target object. Since in this case our target is Sheet 3, we will place our macro in Sheet 3 only. This is because event macros only listens the events with which they are attached to. Here is event macro for this problem.

---

Code:
 

Private Sub Worksheet_Change(ByVal Target As Range)
'Error Handling

On Error Resume Next

    'Check only, if the change happens in Column P.
    If Left(Target.Address(0, 0), 1) = "P" Then
        'Check if selected value changed to Active
        If UCase(Target.Value) = "ACTIVE" Then
            'If Active is selected in drop down list,
            'Row color will change to green.
            Target.EntireRow.Font.Color = RGB(0, 102, 0)
           
        'Check if selected value changed to Deactive
        ElseIf UCase(Target.Value) = "DEACTIVE" Then
            'If Deactive is selected in drop down list,
            'Row color will change to red.
            Target.EntireRow.Font.Color = vbRed
           
        'Check if it's something else than Active or Deactive
        '(Not possible but have been put for validation sake.
        Else
            Target.EntireRow.Font.Color = vbBlack
        End If

    End If

'Turn off Error Handling

On Error GoTo 0

End Sub

---

Example: How and where the macro is to be placed?
Link to Solution File.

---