Change Row Color based on Cell String Match

[Stuart]

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"


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).SetFirstPriori ty
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.

[westconn1]

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

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

[Bob Phillips]

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

[westconn1]

Conditional formatting is the way to go.
i thought so but i did not see how to set colour to entire row

[Bob Phillips]

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.

[Stuart]

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


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.

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.

[Bob Phillips]

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

[Stuart]

Thank you so much!