Conditional Formatting a Range row by row-CodePudding

I'm trying to apply some conditionals rules using VBA in a Range. But I'm very new with conditional formating VBA so I'm a bit lost.

My Users can add rows above of the target range, that mean the range address could be always different.

let's admit that for the exemple, my range is Worksheets("test").Range("MyBoard") ("MyBoard" is my range name, currently located at A19:O32)

How can I apply a rule to turn yellow each rows of my range if the first column contains the value "Customer" ?

Sub FormatRange()
   Dim MyRange As Range
   Set MyRange = Worksheets("test").Range("MyBoard")

   MyRange.FormatConditions.Delete
   MyRange.FormatConditions.Add Type:=xlCellValue, Formula1:="=COUNTIF(MyRange;"*Customer*") > 0"
   MyRange.FormatConditions(1).Interior.Color = RGB(255, 255, 0)
End Sub

Thanks for the help

CodePudding user response：

Conditional formatting has some very particular format to get an entire row to work.

E.g., If i want to apply a color to each row, between certain columns of a specified range:

With .Range(.Cells(1, startColumn), .Cells(lastRow, endColumn))
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$A1>1"
    .FormatConditions(1).Font.Italic = True
End With

Edit1: Indicating use of Find() for the row containing "Customer" being used for the above code.

Sub test()
    With Sheets(1)
        Dim customerCell As Range:  Set customerCell = .Columns(1).Find("Customer")
        If customerCell Is Nothing Then Exit Sub
        Dim lastRow As Long:  lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Cells.FormatConditions.Delete
        With .Range(.Cells(customerCell.Row, 1), .Cells(lastRow, 10))
            .FormatConditions.Add Type:=xlExpression, Formula1:="=CountIf($A" & customerCell.Row & ",""*Customer*"")"
            .FormatConditions(1).Interior.Color = RGB(255, 255, 0)
        End With
    End With
End Sub

CodePudding user response：

I think, this is what your are looking for:

Sub FormatRange()
   Dim MyRange As Range
   Set MyRange = Worksheets("test").Range("MyBoard")

    Dim startAddress As String
    startAddress = MyRange.Cells(1, 1).Address(False, True) ' will return e.g. $A19 in your case
    
    Dim formula As String
    'formula = startAddress & " = ""customer"""      'exact customer
    formula = "ISNUMBER(FIND(""customer""," & startAddress & "))" ' *customer*
    
    Dim fc As FormatCondition
    With MyRange
        .FormatConditions.Delete
        Set fc = .FormatConditions.Add(xlExpression, Formula1:="=" & formula)
        fc.Interior.Color = RGB(255, 255, 0)
    End With
    
End Sub

You have to reference the first cell within your range - and "fix" the column --> .Address(False, True) will return $A19 in your case.

Then you need to build a valid string for the formula to pass to the format condition

You need double quotes for "customer" when building the string.

CodePudding user response：

Please, use the next adapted code:

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)
End Sub