Does not contain VBA code

[NoxGalleon106]

Hi,

I have a spreadsheet that needs to highlight cell on column A if column D is >=20 and column O does not contain “TOR”.

My current codes are below but its not capturing the condition for column O. Can you help me correct my codes?


Range (“A4”). End(xldown).activate
Do until activecell.row= 3
Activecell.offset(-1,0).select
If activecell.offset(0,3).value >= 20 and activecell.offset (0,14).value <> “*TOR*” then
Activecell.interior.color= rgb (192,224,128)
End if
Loop

 

[JLGWhiz]

Maybe this

Range("A4").End(xlDown).Activate
    Do Until ActiveCell.Row = 3
        ActiveCell.Offset(-1, 0).Select
            If ActiveCell.Offset(0, 3).Value >= 20 And InStr(ActiveCell.Offset(0, 14).Value, "TOR") = 0 Then
                ActiveCell.Interior.Color = RGB(192, 224, 128)
            End If
     Loop

 

[Trebor76]

I see JLGWhiz has beaten me to the punch, but here's my effort nonetheless:

Option Explicit
Sub Macro1()

    Dim rngMyCell As Range
    
    Application.ScreenUpdating = False

    'Works on each cell from A3 to A [last row found in Col. A]. Change to suit.
    For Each rngMyCell In Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If Val(Range("D" & rngMyCell.Row)) >= 20 And InStr(Range("O" & rngMyCell.Row), "TOR") = 0 Then
            rngMyCell.Interior.Color = RGB(255, 0, 0) 'Colour cell red if both conditions are met. Change to suit.
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[NoxGalleon106]

I see JLGWhiz has beaten me to the punch, but here's my effort nonetheless:

Option Explicit
Sub Macro1()

    Dim rngMyCell As Range
    
    Application.ScreenUpdating = False

    'Works on each cell from A3 to A [last row found in Col. A]. Change to suit.
    For Each rngMyCell In Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If Val(Range("D" & rngMyCell.Row)) >= 20 And InStr(Range("O" & rngMyCell.Row), "TOR") = 0 Then
            rngMyCell.Interior.Color = RGB(255, 0, 0) 'Colour cell red if both conditions are met. Change to suit.
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

Worked like a charm! Thank you!

 

[Trebor76]

You're welcome. Make sure to also try JLGWhiz's solution as well as they may have spent some time putting it together.

 

[Rick Rothstein]

Hi,

I have a spreadsheet that needs to highlight cell on column A if column D is >=20 and column O does not contain “TOR”.

My current codes are below but its not capturing the condition for column O. Can you help me correct my codes?


Range (“A4”). End(xldown).activate
Do until activecell.row= 3
Activecell.offset(-1,0).select
If activecell.offset(0,3).value >= 20 and activecell.offset (0,14).value <> “*TOR*” then
Activecell.interior.color= rgb (192,224,128)
End if
Loop

Do you have to use VB code? If not, you can use Conditional Formatting. Select the cell from A3 down to the last row in Column A that you want to monitor, the use this Conditional Formatting formula for that selection...

=(D3>=20)*(ISERROR(SEARCH("TOR",O3)))