excel - Color cells based on their value - VBA

Question

I need to be able to colour code cells based on their values in VBA as conditional formatting will not handle the number of conditions I will ultimately require. For instance if the value of B is "Decommission" then I would like to check the values of C, D and E and colour code them in relation to the value of B. Unfortunately, the code I've written is running through the whole sheet and colour coding everything in the range based on the first value. I've defined 2 x ranges (all_data and response) I know what's wrong but I don't know how to tell the code to only restrict the formatting to the value of the row.. Any help would be greatly appreciated.

Sub Formatter()

Dim All_Data As Range
Dim Response As Range

Dim MyCell As Range
Dim MyCell2 As Range

Set All_Data = Range("All_Data")
Set Response = Range("Response")

For Each MyCell In All_Data
If MyCell.Value = "Decommission" Then
   MyCell.Interior.ColorIndex = 3
         For Each MyCell2 In Response
            If MyCell2.Value = "Yes" Then
                MyCell2.Interior.ColorIndex = 4
            End If
         Next
End If
Next

End Sub

Answer 1

Try this:

Sub Formatter()

Dim All_Data As Range
Dim Response As Range

Dim MyCell As Range
Dim MyCell2 As Range

Set All_Data = Range("All_Data")
Set Response = Range("Response")

For Each MyCell In All_Data
If MyCell.Value = "Decommission" Then
   MyCell.Interior.ColorIndex = 3
         For Each MyCell2 In Intersect(Response, ActiveSheet.Rows(MyCell.Row))
            If MyCell2.Value = "Yes" Then
                MyCell2.Interior.ColorIndex = 4
            End If
         Next
End If
Next

End Sub