Using VBA for a conditional formatting with différent rules-CodePudding

How can I set a rules to turn the row backround Yellow and also apply a pattern to some specific columns of this same row, only using VBA ?

Sub FormatRange()
Dim MyRange As Range, listSep As String
Set MyRange = Range("MyBoard")

listSep = Application.International(xlListSeparator)
MyRange.FormatConditions.Delete
MyRange.FormatConditions.Add Type:=xlExpression, formula1:="=ISNUMBER(SEARCH(" & _
              """Customer""" & listSep & MyRange.cells(1, 1).Address(0, 1) & "))"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 255, 0)
MyRange.FormatConditions(1).Interior.Pattern = xlGray75
End Sub

Can I just specify the column letter at the same time as the Range ? (column B/C/E/H)

CodePudding user response：

Please, test the next adapted code. It assumes that you need, **for the same Formula2 condition (formula), to place a specific pattern only on the mention columns (of the named range):

Private Sub FormatRange()
   Dim MyRange As Range, listSep As String
   Set MyRange = Range("MyBoard")

   listSep = Application.International(xlListSeparator)
   MyRange.FormatConditions.Delete
   MyRange.FormatConditions.Add Type:=xlExpression, formula1:="=ISNUMBER(SEARCH(" & _
                    """Customer""" & listSep & MyRange.cells(1, 1).Address(0, 1) & "))"                                                                
   MyRange.FormatConditions(1).Interior.Color = RGB(255, 255, 0)
   
   'the code new part:
   Dim myRng As Range
   
   Set myRng = Intersect(MyRange, MyRange.Parent.Range("B:C, E:E, H:H"))
   myRng.FormatConditions.Delete
   myRng.FormatConditions.Add Type:=xlExpression, formula1:="=ISNUMBER(SEARCH(" & _
                     """Customer""" & listSep & MyRange.cells(1, 1).Address(0, 1) & "))"
   myRng.FormatConditions(1).Interior.Pattern = xlGray75
End Sub