Conditionmal Format VBA-formula

I am trying to create multiple Conditional Format rules using VBA, but have been unable to work out the right syntax.

Using Excel itself I get the correct result as follows:-

> Home > Conditional Formating > Manage Rules > Edit Rule

>>> Rule type ..... formula

>>>Rule description ..... =SEARCH("ISA",$A10)

>>> formats are set

>>>Applies to ..... =$A$10:$Q$100

What is the right way to convert the Rule-description-foumula into VBA?

Thanks

This is the best I have been able to come up with:-

Sub formatting()
'Define the variables
Dim rng As Range
Dim condition1 As FormatCondition
Dim condition2 As FormatCondition


'Set/fix the range for conditional-formatting using the    VBA ‘Range’ function:
Sheets("Pivot-RB").Select
Set rng = Sheets("Pivot-RB").Range("A10:Q100")
rng.Select


'Delete/clear any existing conditional formatting (if any) from the range, using ‘FormatConditions.Delete’ :
rng.FormatConditions.Delete


'Now define and set the criteria and the formats
Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=" & Selection.Cells(1).Address(False, False) & "=MIN(" & Selection.Address & ")")
'fails  Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=SEARCH("ISA",$A10)"
'fails  Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=SEARCH(""ISA"",$A10)"




With condition1
.Font.Color = vbBlue
.Font.Bold = True
.Interior.ColorIndex = 39
End With


With condition2
.Font.Color = vbRed
.Font.Bold = True
End With




End Sub

I have a pivot table whose source data is itself of variable size, and needs to be refreshed frequently.

This causes the pivot table to loose my 6 previous rules of conditional formatting, because the CF range sizes have changed.

I was thinking that if I could write the CF rules by macro then I could auto renew them as needed.

learning how to write CF rules by macro would help in many other workbooks & worksheets when my CF rules-ranges get jumbled up as I copy & paste.

Many of my CF rules use formula, which work, but which I have been unable to convert to VBA.

thanks for your time

Thank you for your reply,

The first thing I originally tried was to record a macro, but it does not write any vba for :-

selecting menu item=cf,

entering the Formula

selecting the formats

etc.

I just tried again, recording the macro with the VBA window open (ALT=F11)

and could see it writing code for normal actions like ...Range("D45").Select

but once I started selecting menu options, nothing got written to the new macro.

that is why I tried finding code examples on the internet, but I couldn't work out the syntax.

The code I found/worked out can work:-

eg:

Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=" & Selection.Cells(1).Address(False, False) & "=MIN(" & Selection.Address & ")")

does work (copied direct from i/n)

but my attempts (among many others) to write the formula I want, such as:

Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=SEARCH("ISA",$A10)

or

Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=SEARCH(""ISA"",$A10)"

get rejected