Coloring font in cell with X then color range in the same row

[joh1135]

I have been trying to get this to work, but can't.

I have some X's in some rows of Columns E2 to E40, that I search on and when it is I then need to format for the row from C to G. One other change is that if the X is deleted then return the font color back to black. I not been able to make it work, I can get my macro to find the X's, but then I can't get the rest figured out.

Thank you for any assistance.

 

[Rick Rothstein]

How about using event code instead of a macro (that way the font in the cells will change automatically no matter what is typed into range E2:E40)?

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("E2:E40")) Is Nothing Then
    Application.EnableEvents = False
    For Each Cell In Intersect(Target, Range("E2:E40"))
      If Cell.Value Like "[Xx]" Then
        Cell.Offset(, -2).Resize(, 5).Font.Color = vbRed
      Else
        Cell.Offset(, -2).Resize(, 5).Font.Color = vbBlack
      End If
    Next
    Application.EnableEvents = True
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.