DuckDuckGo Conditional Format

[MrDB4Excel]

I have read through various sites about adding a conditional format to VBA and found too much confusion. Not being very prolific in creating a VBA macro that can do this I am turning to Mr. Excel in the hopes someone can enlighten me and help to bring this to a conclusion.
First of all, I need this macro to run on any sheet within the workbook.
I have twelve such workbooks, each indicated by the month, i.e. 01-January.xlsm, 02-February.xlsm, 03-March.xlsm, 04-April.xlsm, etc., and each one needs to acquire and have available this macro. This may imply that I don’t want to have to open each of these workbooks and add this macro but that this macro will automatically become available as I open subsequent xlsm files. If not then so be it and I will simply copy this macro into the remaining eleven workbooks.
The macro needs to provide a selection prompt (because each sheet in a workbook will have different range selections), and from there it simply executes the desired attributes of a conditional format. This conditional format is as follows: highlight cell rules, and text that contains: “DuckDuckGo”, the format is simply to highlight these cells in this selected range with yellow fill that contains the text DuckDuckGo.
The attached Xl2bb Mini Sheet is a very small example of what some days produce, sometimes as many as 200 to 500 rows of web searches, and as you can see this is only what I have accumulated thus far today 19 March 2024. Obviously, many days include website visits that range from music, science, physics, cosmos, computer IT, DIY Handyman, religion, philosophy, politics, travel, humor, movies and films, healthcare, recipes, miscellaneous, financial, and this is only a small portion of the things I daily visit. I say this only because a sheet showing hundreds of site visits will only have a smaller portion of those visits relevant to DuckDuckGo.
Any help will be very much appreciated as this is something I do every day, that is, I collect all website history for each day, and before the computer shuts down at day's end, I delete all history. This exercise allows me to save important website visits while cleaning out my C drive.

03-March.xlsm
	A	B	C	D	E
2	D	Excel VBA To Allow User To Select A Range With An InputBox | Get input from the user using Input Box - YouTube	https://www.youtube.com/watch?v=lL8z7WRigK4	IT-Technical-Computer	3/19/2024 07:31
3	Iexc	how to cause vba to ask for user input for a range selection to finish the vba at DuckDuckGo	https://duckduckgo.com/?hps=1&q=how+to+cause+vba+to+ask+for+user+input+for+a+range+selection+to+finish+the+vba&atb=v319-1	DuckDuckGo>>>Excel	3/19/2024 07:29
4	Iexc	how to create a quick access toolbar button that automatically applies a special conditional format to a selected range at DuckDuckGo	https://duckduckgo.com/?hps=1&q=how+to+create+a+quick+access+toolbar+button+that+automatically+applies+a+special+conditional+format+to+a+selected+range+&atb=v319-1	DuckDuckGo>>>Excel	3/19/2024 07:25
5	D	Is there a way to apply conditional formatting with one click? - Microsoft Community	https://answers.microsoft.com/en-us/msoffice/forum/all/is-there-a-way-to-apply-conditional-formatting/84868157-d819-4cba-a69c-099f88ce7515	IT-Technical-Computer	3/19/2024 07:25
6	D	Using Conditional Formatting with Excel VBA - Auto VBA	https://www.autovbax.com/learn/vba/conditional-formatting.html	IT-Technical-Computer	3/19/2024 07:41
7	D	Using Conditional Formatting with Excel VBA - Automate Excel	https://www.automateexcel.com/vba/conditional-formatting/	IT-Technical-Computer	3/19/2024 07:27
8	D	VBA To Allow User To Select A Range With An InputBox	https://www.thespreadsheetguru.com/select-range-with-inputbox/	IT-Technical-Computer	3/19/2024 07:29
9	Iexc	vba to create a special conditional format to a selected range at DuckDuckGo	https://duckduckgo.com/?hps=1&q=vba+to+create+a+special+conditional+format+to+a+selected+range&atb=v319-1	DuckDuckGo>>>Excel	3/19/2024 07:27
10	E	HERE GOES NOTHING | Spray Foaming Our Shipping Container - YouTube	https://www.youtube.com/watch?v=uamfKXoNaZI	DIY-Handyman-Misc	3/19/2024 08:11
11	E	Off Grid Solar Container Home Vs. Spring Hail Storms | Green House Prep - YouTube	https://www.youtube.com/watch?v=CWTUNYSot6Q	DIY-Handyman-Misc	3/19/2024 08:15
12	J	When patients ask if a crown can be biomimetic...✨ #shorts #dentalcrowns #dentist - YouTube	https://www.youtube.com/shorts/p5aEp2_T3cw	Health-Life-Homecare	3/19/2024 08:12
_19

Cell Formulas
Range	Formula
D2:D12	D2	=IF((ISERROR(XLOOKUP(A2,'01Sort-Types'!$R$47:$R$73,'01Sort-Types'!$S$47:$S$73,0))),"",(XLOOKUP(A2,'01Sort-Types'!$R$47:$R$73,'01Sort-Types'!$S$47:$S$73,0)))

 

[kvsrinivasamurthy]

This code will apply CF for DUckDuckGo in the activesheet.
This code can be in any workbook. In the macro dialogue box select "All open workbooks".
Open any other file. then run this macro.

Sub High_Light()
Dim Rng As Range
Set Rng = ActiveSheet.UsedRange
    Rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ISNUMBER(FIND(""DuckDuckGo""," & Replace(Rng.Cells(1, 1).Address, "$", "") & "))"
    Rng.FormatConditions(Rng.FormatConditions.Count).SetFirstPriority
    With Rng.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Rng.FormatConditions(1).StopIfTrue = False
End Sub

 

[MrDB4Excel]

This code will apply CF for DUckDuckGo in the activesheet.
This code can be in any workbook. In the macro dialogue box select "All open workbooks".
Open any other file. then run this macro.

Sub High_Light()
Dim Rng As Range
Set Rng = ActiveSheet.UsedRange
    Rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ISNUMBER(FIND(""DuckDuckGo""," & Replace(Rng.Cells(1, 1).Address, "$", "") & "))"
    Rng.FormatConditions(Rng.FormatConditions.Count).SetFirstPriority
    With Rng.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Rng.FormatConditions(1).StopIfTrue = False
End Sub

Hello kvsrinivasamurthy,

Thank you, this got me off to a good start. I did make one minor adjustment to the range as you can see in the following code and it worked very well with your code but I wanted to select the range via an input callout.

Sub High_Light()
Dim Rng As Range
Dim FormatRuleInput As String

'Get A Cell Address From The User to Get Number Format From
On Error Resume Next
Set Rng = Application.InputBox( _
Title:="Number Format Rule From Cell", _
Prompt:="Select a cell to pull in your number format rule", _
Type:=8)
On Error GoTo 0

Rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(FIND(""DuckDuckGo""," & Replace(Rng.Cells(1, 1).Address, "$", "") & "))"
Rng.FormatConditions(Rng.FormatConditions.Count).SetFirstPriority
With Rng.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Rng.FormatConditions(1).StopIfTrue = False
End Sub

 

[kvsrinivasamurthy]

You should select not a single cell but range of cells for CF. Code is working ok?

 

[MrDB4Excel]

You should select not a single cell but range of cells for CF. Code is working ok?

Yes, the code is working well. Both your version and my edited version.
Thanks loads, very much appreciated, a very big time saver. This can also be altered to apply to other text strings.
Awesome macro!

 

[kvsrinivasamurthy]

Ok. Happy that your problem solved.

 

[MrDB4Excel]

Well, it worked for quite some time until today. It still works but now I must select the enable-disable popup in order for the macro to run. I first added the macro in the "ThisWorkbook" in the file 03-March.xlsm.
Now when I run it in 04-April.xlsm I get this enable-disable popup and the file 03-March is opened. But what is odd is that it worked fine in the file 04-April.xlsm on the _01 tab (or sheet) but now for _02 and other sheets, it gives this enable-disable popup.
Is there some way for this macro to truly be available in all sheets for any workbook? Perhaps somehow there exists some kind of macro link to 03-March.xlsm.
Any help will be much appreciated so I don't have to constantly select "Enable" from the enable-disable popup.

 

[kvsrinivasamurthy]

This is security connected to data in other files. This has got nothing to do with Macro, I feel. The sheets are having links with another file. Before running macro the data has to updated. If the files linked are safe enable automatic links update.

 

[MrDB4Excel]

The problem is that the only file that links to any workbook is KutoolsforExcel.xlam. This file has been on my PC for years and has been available as needed for Kutools. So there must be something about where this code is best to be inserted. Perhaps some xlam file? I am at a loss as to why this isn't working on other workbooks in succession from the original file where this code was placed in the "ThisWorkbook". The original file: 03-March.xlsm. Successive files: 04-April.xlsm, 05-May.xlsm, 06-June.xlsm etc., etc....
Perhaps this code needs to be somewhere specific to any workbook that is opened.

 

[MrDB4Excel]

I got it working. I created an xlam file and now it is simply an addin. Works in any workbook.
Thanks for causing me to put my thinking cap on.