VBA to change cell interior color based search for specific text in two columns

[Mookiemines]

I have script that will look that will search for specific text within a cell in a specified column. If found, it will change the interior color for the row of the table. If not found, the script will loop to the next cell in the column until the last column is reached. This script is working well, however, I would like to add a second search in another column if the specific text is found in the first search. It would need to loop through the results from the first search to look in the other column for a value that is >499. Only if both criteria are would the row's interior color change.

I cannot figure out how to add the second criteria.

Result from current VBA:

Desired result after second criteria is found:

Current VBA script:

Dim SrchRng3 As Range
Dim c3 As Range, f As String

'DF
    Set SrchRng3 = ActiveSheet.Range("I1", ActiveSheet.Range("I65536").End(xlUp))
    Set c3 = SrchRng3.Find("DF", LookIn:=xlValues)
        If Not c3 Is Nothing Then
    f = c3.Address
    Do
    With ActiveSheet.Range("A" & c3.Row & ":N" & c3.Row)
        .Font.ColorIndex = 2
        .Interior.ColorIndex = 11
     End With
 
    Set c3 = SrchRng3.FindNext(c3)
    Loop While c3.Address <> f
    End If

Any suggestions would be greatly appreciated.

 

[kevin9999]

You could probably achieve this with CF, but if you're set on a VBA solution, try this (run from the active sheet)

Option Explicit
Sub Mookiemines()
    Dim lr As Long, c As Range
    lr = Cells(Rows.Count, 9).End(xlUp).Row
    Application.ScreenUpdating = False
   
    For Each c In Range("I2:I" & lr)
        If c = "DF" And c.Offset(, 4) > 499 Then
            With c.Offset(, -8).Resize(, 14)
                .Font.ColorIndex = 2
                .Interior.ColorIndex = 11
            End With
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

 

[Siddharth Rout]

I agree with Kevin. Conditional Formatting is the way to go. But if you want VBA then you have achieve conditional formatting from VBA as well

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range
    
    '~~> Set this to the relevant worksheet
    Set ws = Sheet1
    
    With ws
        '~~> Find the last row
        '~~> Avoid hard coding to 65536 or 1048576
        lRow = .Range("I" & .Rows.Count).End(xlUp).Row
        
        '~~> Set your range, excluding headers
        Set rng = .Range("A2:N" & lRow)
        
        '~~> Apply conditional formatting
        With rng
            .FormatConditions.Delete
            
            .FormatConditions.Add Type:=xlExpression, _
                                  Formula1:="=AND($I2=""DF"",$M2>499)"
            
            '~~> Change to relevant color here
            With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent5
                .TintAndShade = 0
            End With
        End With
    End With
End Sub

Benefits of this method

1. You do not have to loop. Code will be faster.
2. If the value changes in the worksheet , you do not have to run the code again unless the range expands. Conditional formatting will take care of it.

 

[Mookiemines]

Thank you Kevin and Sid!

Used the following and achieved my desired result:

Sub DF_500()

Dim Target As Range

With Worksheets(1)
Set Target = .Range("A2:N2", .Range("A" & .Rows.Count).End(xlUp))
End With

Target.FormatConditions.Delete

Target.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($I2="" DF"",$M2>=500)"

With Target.FormatConditions(1)
.Font.ColorIndex = 2
.Interior.ColorIndex = 11
End With

End Sub

 

[Siddharth Rout]

Thank you Kevin and Sid!

Used the following and achieved my desired result:

Sub DF_500()

Dim Target As Range

With Worksheets(1)
Set Target = .Range("A2:N2", .Range("A" & .Rows.Count).End(xlUp))
End With

Target.FormatConditions.Delete

Target.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($I2="" DF"",$M2>=500)"

With Target.FormatConditions(1)
.Font.ColorIndex = 2
.Interior.ColorIndex = 11
End With

End Sub

There is only going to be 1 worksheet in your workbook then there is no issue using

With Worksheets(1)

But if there are going to be more than 1 worksheet then using Worksheets(1) is a bad idea. I would recommend declaring a worksheet variable and initializing it using the sheet codename or the sheet name.