Using VBA for a conditional formatting with différent rules

Question

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)

I am afraid I cannot understand what you mean by "Can I just specify the column letter at the same time as the Range ? (column B/C/E/H)"... Do you mean to apply the FormatConditions for only a column, or for discontinuous columns range? Something like "B2:C10", "E2:E10", "H2:H10" — FaneDuru
– FaneDuru, Commented Aug 8, 2022 at 10:52
My row has already a yellow background but now I want to apply a specific pattern to the colomn A, C, E and H of this row. dépending of the target (here it's customer) the pattern will be apply to different column. — Xodarap
– Xodarap, Commented Aug 8, 2022 at 10:55
Do you mean that "MyBoard" named range contains the mentioned columns and separated of the FormatConditions already applied with the above code, do you want a second one to be applied only to the mentioned columns (inside the named range)? — FaneDuru
– FaneDuru, Commented Aug 8, 2022 at 10:58
Ho yes of course, All columns and row are part of "MyBoard" range. A second one could be good but with the same rules as the first one (yellow background) — Xodarap
– Xodarap, Commented Aug 8, 2022 at 10:59
If so, please show us the address of this range. Then, do you want applying the pattern based on the same formula2 condition? — FaneDuru
– FaneDuru, Commented Aug 8, 2022 at 10:59

FaneDuru · Accepted Answer · 2022-08-08 11:23:30Z

2

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

answered Aug 8, 2022 at 11:23

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

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

2 Comments

Xodarap Over a year ago

It's perfect, thanks a lot ! I just added a yellow background at the same time as the pattern and now I got exactly what I want

FaneDuru Over a year ago

@Xodarap Yes, the previously added Interior (background) has been deleted by myRng.FormatConditions.Delete... I was not sure that the Yellow interior color must also exist in that discontinuous range. I did not test the code, I only thought it will work. Anyhow, I did not understand if the Yellow part must exist in the second created range and I thought that you can take care of it, if necessary... :)

Collectives™ on Stack Overflow

Using VBA for a conditional formatting with différent rules

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related