Conditional Formatting a Range row by row

Question

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

Is "MyBoard" a named ranges? If so, how many column does it have? Then, the formula must count the specific cell value in the range first column... — FaneDuru
– FaneDuru, Commented Aug 5, 2022 at 12:32
You may need to change your formula, e.g., Type:=xlExpression, Formula1:="=$A1>1", where the $A1 is important to allow the formatting to occur on that specific row. — Cyril
– Cyril, Commented Aug 5, 2022 at 12:32

FaneDuru · Accepted Answer · 2022-08-05 17:02:37Z

1

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

edited Aug 5, 2022 at 17:02

answered Aug 5, 2022 at 12:59

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

23 Comments

FaneDuru Over a year ago

@Xodarap Didn't you find some time to test the above code? I updated it. In case of Formatcontitions formula, the separator should be the local list separator (according to localization...).

FaneDuru Over a year ago

@Xodarap Is it so difficult to test the code and send some feedback?

Xodarap Over a year ago

Hello @FaneDuru I just didn't found the time to test your code last friday. But I just tried it and it's interesting. I have an issue with the formula. I convert it into french and it's working as a "normal rule" in conditional formating option but not by using VBA. The formula keep all "" on it. so nothing happen. I try to find a solution

Xodarap Over a year ago

In french the formula is : MyRange.FormatConditions.Add Type:=xlExpression, formula1:="=ESTNUM(CHERCHE(" & """Customer""" & listSep & MyRange.cells(1, 1).Address(0, 1) & "))" and when I apply this formula I don't get any error but when I check in Conditional formating the formule is : ="ESTNUM(CHERCHE(""Customer"";$A14))"

Xodarap Over a year ago

Ho that's good to know ! Thanks for all the help. I will adapt it for all the other formating I have to do. It should be ok from now

|

Cyril · Accepted Answer · 2022-08-05 13:42:03Z

1

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

edited Aug 5, 2022 at 13:42

answered Aug 5, 2022 at 12:34

Cyril

6,8291 gold badge21 silver badges33 bronze badges

11 Comments

Xodarap Over a year ago

Ok that's very different of what I expected, In each formula I found they all start at A1 but my range start (currently) at A19 so how can I adapt that to my situation ? I mean it should be dynamic isn't ? today it's A19 but tommorow it could be A40

Cyril Over a year ago

@Xodarap You can use Range.Find().Row to determine where the first instance of "Customer" occurs, and use that row for the start. Let me give an update to my answer.

Cyril Over a year ago

@Xodarap just posted update; note that I used columns 1 to 10 in the example code.

Xodarap Over a year ago

Well this solution work ! but only for the row who contains exactly "Customer" in the first column. How can I edit it to apply it also to all row with column "Old Customer" / "New Customer" / "Customer 2021" / "Customer 2022" ??

Cyril Over a year ago

@Xodarap Formula1:="=CountIf($A" & customerCell.Row & ",""*Customer*"")". I updated the post. You had the CountIf() in yours and it would be the way to do it... note the double quotes, "" is used in this code because you want an actual quotation mark to appear in the string.

|

Ike · Accepted Answer · 2022-08-05 13:53:21Z

0

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.

edited Aug 5, 2022 at 13:53

answered Aug 5, 2022 at 12:51

Ike

13.4k6 gold badges20 silver badges38 bronze badges

6 Comments

Xodarap Over a year ago

Sadly I got an error at the row : Set fc = .FormatConditions.Add(xlExpression, Formula1:="=" & formula) the error is : incorrect call or argument

Ike Over a year ago

Made a change to the formula - it is a bit tricky for me to test as I have a German excel - and there you have to put the german version of the formula for format condition

Xodarap Over a year ago

Same story for me with a deprecated french version ...

Ike Over a year ago

Will there be users of the sheet with other languages? In French you would need "ESTNUM(TROUVE("customer""," & startAddress & "))" --> en.excel-translator.de/translator helps translating the formulas.

Ike Over a year ago

Regarding an international version, look here: stackoverflow.com/q/69220627/16578424

|

Collectives™ on Stack Overflow

Conditional Formatting a Range row by row

3 Answers 3

23 Comments

11 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

23 Comments

11 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related