Change Row Color based on Cell String Match

Alright, I realize that this is a fairly commonly asked question, but I've tried my best to figure this out on my own.

I am creating a protected worksheet for my team to use. So far, this has been a hack and slash job as I am far from proficient in Excel, but I have managed to make it work to some extent. However, I have realized that many individuals are unable to follow simple instructions, and thus, keep "breaking" my spread sheet. Thus, I've set off to create a "fool proof" system to avoid these problems.

The sheet seeks to track how long a user spent on particular pieces of data. There are multiple types of documents to track.

One column, F, specifies the type of document to enter. Users enter a string value of VD, RM, FA, NFA, or QC, or some combination of them.

If the user has put FA or NFA in column F, I need that entire row to be highlighted. The color I need is "16764108"

Code:

---

Sub Highlight_Apps()
'
' Highlight_Apps Macro
'
' Keyboard Shortcut: Ctrl+r
'
    Cells.FormatConditions.Delete
   
   
   
    Range("A6:J205").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OR(NOT(ISERROR(SEARCH(""FA"", F6:F205))))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 16764108
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
   
   
   
End Sub

---

This code *works*... but I am only seeing the corresponding cell in column A receive the proper color. The rest remain unchanged.

Formatting needs to apply from A6:J205 (A1:J5 are just a header and other necessary calculations that are currently functioning).

I understand that with this macro I will have to ask users to enable macros as well as press a key combination (I've been using CTRL + R) prior to printing, but I can only hand hold so much.

I appreciate any and all help on this matter! I am leaving the office soon, but I will try to answer from home as well.

Note:
Sometimes not every cell will be filled in within the row, which from what I have read may make this more complicated, please keep this in mind if this changes your code.

if you are going to use conditional formatting then you should not require any vba code any way, and probably does not require macros enabled, but will not set colour to row

you can use application events to save user pressing buttons like

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 6 Then
 
  If Target = "FA" Or Target = "NFA" Then Target.EntireRow.Interior.Color = 16764108 Else Target.EntireRow.Interior.ColorIndex = xlColorIndexNone
  End If

End Sub

---

Conditional formatting is the way to go.

Select A6:J205
Ribbon Home>Styles>Conditional Formatting>New Rule
Select 'Use a formula to determine which cells to format'
Input a formula of =OR($F6="FA",$F6="NFA")
Click Format...
Select the Fill tab
Choose your colour
Then OK out

Quote:

---

Conditional formatting is the way to go.

---

i thought so but i did not see how to set colour to entire row

He doesn't need, nor want, to colour the entire row, his data only extends to column J. But the whole row is just as easy, just select whole rows.

Thanks to those that have replied so far! Much appreciated.

Quote:

---

Originally Posted by xld

He doesn't need, nor want, to colour the entire row, his data only extends to column J. But the whole row is just as easy, just select whole rows.

---

Correct, I don't need the *entire* row covered, just from A through J, when the string "FA" is found.

Currently, this is what my spreadsheet looks like. I had to make sure I was able to clear out some of the company data before posting this.

Attachment 11015

Final Edit: Alright, so, I do believe I want to continue to use Conditional Formatting, which is what my macro attempted to do. However, I need to macro in order to REAPPLY the conditional formatting that users may have messed up with copy and paste without choosing Paste Values.

Code:

---

Public Sub ApplyCF()
Const FORMULA_CF As String = "=OR($F7=""FA"",$F7=""NFA"")"
Dim rng As Range
Dim i As Long

    With ActiveSheet
   
        Set rng = .Range(.Range("A7"), .Range("A7").End(xlDown)).Resize(, 10)
        With rng
       
            For i = .FormatConditions.Count To 1 Step -1
           
                .FormatConditions(i).Delete
            Next i
       
            .FormatConditions.Add Type:=xlExpression, Formula1:=FORMULA_CF
            .FormatConditions(1).Interior.ColorIndex = 40
            .FormatConditions(1).StopIfTrue = False
        End With
    End With
End Sub

---

Thank you so much!