VBA - Color entire row based on text

dcme27

New Member
Joined
Dec 9, 2019
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Sorry Ignore my Previous post, only insert your macro here and tell exactly which color you want for each condition?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
Will "Service Reference" be the only thing in the cell, or is it part of a larger string?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Assuming it's on its own in the cell, try
VBA Code:
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
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Forum statistics

Threads
1,147,747
Messages
5,742,966
Members
423,769
Latest member
LongToast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top