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?
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