variant with conditional formatting

[288enzo]

I'm having a difficult time with how I can use a variant with a conditional formatting formula.

This is what I have

    Dim rng As Variant
    Set rng = Range("V2:V" & Range("A" & Rows.Count).End(xlUp).Row)
    
    rng.FormatConditions.Add Type:=xlTextString, String:="No", TextOperator:=xlContains
    rng.FormatConditions(1).Interior.Color = 255 ' Red
    rng.FormatConditions(1).Font.ThemeColor = xlThemeColorDark1
    rng.FormatConditions(1).Font.Bold = True
    rng.FormatConditions(1).StopIfTrue = False
    
    rng.FormatConditions.Add Type:=xlTextString, String:="Yes", TextOperator:=xlContains
    rng.FormatConditions(2).Interior.Color = 5287936 ' Green
    rng.FormatConditions(2).Font.ThemeColor = xlThemeColorDark1
    rng.FormatConditions(2).Font.Bold = True
    rng.FormatConditions(2).StopIfTrue = False
    
    rng.Offset(0, 1).FormatConditions.Add Type:=xlExpression, Formula1:="=$V$35=""No""" 'this is where I'm hung up
    rng.FormatConditions(3).Interior.Color = 255 ' Red
    rng.FormatConditions(3).Font.ThemeColor = xlThemeColorDark1
    rng.FormatConditions(3).Font.Bold = True
    rng.FormatConditions(3).StopIfTrue = False

condition3 is where I'm having trouble. What I'm trying to accomplish is if any cell in column V = "No", then apply the red background to both cells in V and W. Perhaps there is a better way to write this?

Thank you

 

[Fluff]

How about

Rng.Resize(, 2).FormatConditions.Add Type:=xlExpression, Formula1:="=$V2=""No""" 'this is where I'm hung up

 

[288enzo]

How about

Rng.Resize(, 2).FormatConditions.Add Type:=xlExpression, Formula1:="=$V2=""No""" 'this is where I'm hung up

Got it, so the formula only needs to point at the first cell. Thanks.

I made the change and the condition was created, but stopped when it tried to apply the formatting.

This worked to create the condition

rng.Offset(0, 1).FormatConditions.Add Type:=xlExpression, Formula1:="=$V$2=""No"""

Run-time error '9': Subscript out of range

rng.FormatConditions(3).Interior.Color = 255

I got the same error when I added the offset

rng.Offset(0, 1).FormatConditions(3).Interior.Color = 255

 

[Fluff]

Do you want both cols V & W to get the red fill?

 

[288enzo]

Do you want both cols V & W to get the red fill?

Yes, that would be ideal to merge both conditions (1 & 3).

 

[Fluff]

Should that also apply to the Yes?

 

[288enzo]

Should that also apply to the Yes?

No, the Yes should only effect those cells in column V.

 

[Fluff]

Ok, how about

    Dim rng As Range
    Set rng = Range("V2:V" & Range("A" & Rows.Count).End(xlUp).Row)
    
    With rng
      .FormatConditions.Add Type:=xlTextString, String:="Yes", TextOperator:=xlContains
      With .FormatConditions(.FormatConditions.Count)
         .Interior.Color = 5287936 ' Green
         .Font.ThemeColor = xlThemeColorDark1
         .Font.Bold = True
         .StopIfTrue = False
      End With
   End With
    
   With rng.Resize(, 2)
      .FormatConditions.Add Type:=xlExpression, Formula1:="=$V2=""No"""
      With .FormatConditions(.FormatConditions.Count)
         .Interior.Color = 255 ' Red
         .Font.ThemeColor = xlThemeColorDark1
         .Font.Bold = True
         .StopIfTrue = False
      End With
   End With

 

[288enzo]

Ok, how about

    Dim rng As Range
    Set rng = Range("V2:V" & Range("A" & Rows.Count).End(xlUp).Row)
   
    With rng
      .FormatConditions.Add Type:=xlTextString, String:="Yes", TextOperator:=xlContains
      With .FormatConditions(.FormatConditions.Count)
         .Interior.Color = 5287936 ' Green
         .Font.ThemeColor = xlThemeColorDark1
         .Font.Bold = True
         .StopIfTrue = False
      End With
   End With
   
   With rng.Resize(, 2)
      .FormatConditions.Add Type:=xlExpression, Formula1:="=$V2=""No"""
      With .FormatConditions(.FormatConditions.Count)
         .Interior.Color = 255 ' Red
         .Font.ThemeColor = xlThemeColorDark1
         .Font.Bold = True
         .StopIfTrue = False
      End With
   End With

Perfect, thank you. I haven't used Resize before. Nice tip, thanks again

 

[Fluff]

You're welcome & thanks for the feedback.