Conditional Format Cells Fill based on criteria

you should just be able to replace "Selection" with "Range()", for example:

With Range("A:D").FormatConditions(1)
    With .Interior
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
    End With
    .StopIfTrue = False\`
End With

VBA Conditional Formatting

• Your condition is more complex than the simple ones offered by Excel hence you need to use a formula, e.g.:

  =ISNUMBER(FIND("(",$A2))
  

  Copy this formula to F2. Then copy F2 across to F2:I11 to better understand how it works.

Sub ApplyCF()
    
    ' Define constants.
    Const SHEET_NAME As String = "Sheet1"
    Const TOP_ROW_ADDRESS As String = "A2:D2"
    Const CRITERIA_COLUMN As Long = 1
    Const CRITERIA_STRING As String = "("
    Const CF_COLOR As Long = 10092543

    ' Reference the workbook and worksheet.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets(SHEET_NAME)
    
    ' Delete existing Conditional Formatting from the first data row
    ' to the bottom row of the sheet and reference the data range.
    ' Exit if no data.
    Dim rg As Range, lcell As Range
    With ws.Range(TOP_ROW_ADDRESS)
        With .Resize(ws.Rows.Count - .Row + 1) ' (A2:D1048576)
            .FormatConditions.Delete
            Set lcell = .Find( _
                What:="*", LookIn:=xlFormulas, SearchDirection:=xlPrevious)
            If lcell Is Nothing Then
                MsgBox "No data found in ""'" & ws.Name & "!'" _
                    & .Address(0, 0) & """!", vbExclamation
                Exit Sub
            End If
        End With
        Set rg = .Resize(lcell.Row - .Row + 1)
    End With
    
    ' Build the Conditional Formatting formula.
    Dim cfFormula As String: cfFormula = "=ISNUMBER(FIND(""" _
        & CRITERIA_STRING & """," & rg.Cells(CRITERIA_COLUMN).Address(0) & "))"
    
    ' Apply the formatting to the data range.
    With rg.FormatConditions.Add(Type:=xlExpression, Formula1:=cfFormula)
        .SetFirstPriority
        .Interior.Color = CF_COLOR
        .StopIfTrue = False
    End With
    
End Sub

The condition you've added checks if the cell contains an open bracket - so if A1 contains it, it colours A1. If B1 contains it, it colours B1, etc.

You need to change that to a formula that says if column A contains an open bracket.

The formula =FIND("(",$A1)>0 will return TRUE if an open bracket is found within the cell, or an error otherwise. FIND will find the position of the bracket and the >0 asks if it's greater than 0.

Also have a look at How can I avoid using Select in Excel

Basically what @VBasic20008 put but with less code (ISNUMBER or >0 would work) - ISNUMBER would return TRUE/FALSE rather than TRUE/Error though.

Public Sub Test1()
    Columns("A:D").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=FIND(""("",$A1)>0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub