If two conditions met, highlight cell VBA

[mbkinzer]

Hello,

I would like for the macro to read column K (11) down to my last row (lrow) and if the cell contains "Bus driver" and the same row in Column O (15) reads "$0.00" to highlight the cells.
Currently nothing is happening so I feel like I am way off. Any help would be appreciated!

My current macro reads:

If Range(Cells(lrow, 11), Cells(lrow, 11)) = "Bus Driver" And Range(Cells(lrow, 15), Cells(lrow, 15)) = "0.00" Then
Range(Cells(lrow, 11), Cells(lrow, 11)).Interior.ColorIndex = 15
Else
Range(Cells(lrow, 11), Cells(lrow, 11)).Interior.ColorIndex = xlNone
End If

 

[kevin9999]

Try this.

Option Explicit
Option Compare Text
Sub BusDriver()
    Dim lr As Long, c As Range
    lr = Cells(Rows.Count, "K").End(3).Row
    Range("K2:K" & lr).Interior.ColorIndex = xlNone
    For Each c In Range("K2:K" & lr)
        If c = "Bus driver" And c.Offset(, 4) = 0 Then c.Interior.ColorIndex = 15
    Next c
End Sub

 

[Zot]

In column O, the value is text or number? Declaring as "0.00" will make looks for text/string instead of number value.
Here is another alternative

Sub ColorZero()

Dim nRow As Long, eRow As Long
Dim ws As Worksheet

' Assuming the sheet name is Sheet1. Rename accordingly
Set ws = ActiveWorkbook.Sheets("Sheet1")
' Find the last row with data
eRow = ws.Cells(ws.Rows.Count, "K").End(xlUp).Row
' Assuming initial data row is 2. Change if required
For nRow = 2 To eRow
    If ws.Range("K" & nRow) = "Bus driver" And ws.Range("O" & nRow) = 0 Then
        ws.Range("K" & nRow).Interior.ColorIndex = 15
    Else
        ws.Range("K" & nRow).Interior.ColorIndex = xlColorIndexNone
    End If
Next

End Sub

 

[mbkinzer]

In column O, the value is text or number? Declaring as "0.00" will make looks for text/string instead of number value.
Here is another alternative

Sub ColorZero()

Dim nRow As Long, eRow As Long
Dim ws As Worksheet

' Assuming the sheet name is Sheet1. Rename accordingly
Set ws = ActiveWorkbook.Sheets("Sheet1")
' Find the last row with data
eRow = ws.Cells(ws.Rows.Count, "K").End(xlUp).Row
' Assuming initial data row is 2. Change if required
For nRow = 2 To eRow
    If ws.Range("K" & nRow) = "Bus driver" And ws.Range("O" & nRow) = 0 Then
        ws.Range("K" & nRow).Interior.ColorIndex = 15
    Else
        ws.Range("K" & nRow).Interior.ColorIndex = xlColorIndexNone
    End If
Next

End Sub

This worked for me. Thank you! What if I wanted to highlight if the cell in Column K contained the word "Bus driver" instead of it being = to bus driver?

 

[bebo021999]

instead of
="bus driver"
try partial match by:
like "*bus driver*"

 

[Zot]

Like @bebo021999 said use operator Like

If ws.Range("K" & nRow) Like "*Bus driver*" And ws.Range("O" & nRow) = 0 Then

However, Like is case sensitive. So, if you want it to insensitive you need to put Option Compare Text at the top of your module

 

[mbkinzer]

Like @bebo021999 said use operator Like

If ws.Range("K" & nRow) Like "*Bus driver*" And ws.Range("O" & nRow) = 0 Then

However, Like is case sensitive. So, if you want it to insensitive you need to put Option Compare Text at the top of your module

Thank you so much!!