VBA - Color entire row based on text

[dcme27]

I need help adding a coloration to my existing column hiding Macro. I see a lot of statements that Conditional Formatting is better, but I need this to be available to dozens of people on brand new spreadsheets they export into Excel. Currently I have the Macro added to my Custom Toolbar and will be doing the same for everyone else.

So if there's a way to develop a Conditional Format option that I can connect to my existing VBA to allow for the single button press, that will be great. But the people using this will be using it half a dozen times a day and I don't want them to have to add the Conditional Formatting for each time.

The goal: Some of the rows will have "Service Reference" in Column J. These lines are reference lines for all the other lines and so I want to be able to separate them out from the rest with a bright yellow highlight across the row. I've found a few online but they all seem extremely specific and I haven't been able to re-formulate for my purposes.

I greatly appreciate any help that can be provided.

 

[maabadi]

1. Please upload example data with XL2BB add-in (Preferable) OR upload at free hosting site e.g. GoogleDrive, OneDrive, DropBox and Insert Link Here
2. Please Update your Account details (click your user name at the top right of the forum) so we know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[maabadi]

Sorry Ignore my Previous post, only insert your macro here and tell exactly which color you want for each condition?

 

[Fluff]

Will "Service Reference" be the only thing in the cell, or is it part of a larger string?

 

[Fluff]

Assuming it's on its own in the cell, try

Sub dcme()
   With Range("J1", Range("J" & Rows.Count).End(xlUp))
      .Replace "Service Reference", True, xlWhole, , False, , False, False
      .SpecialCells(xlConstants, xlLogical).EntireRow.Interior.Color = vbYellow
      .Replace True, "Service Reference", xlWhole, , False, , False, False
   End With
End Sub

 

[dcme27]

Will "Service Reference" be the only thing in the cell, or is it part of a larger string?

That was the answer! Thank you so much!

 

[Fluff]

Glad to help & thanks for the feedback.