Cell highlighting using dba

[Bamh1]

Hello,
I'm trying to write a code that highlights cells greater than 45 min ( in hh mm ss format) in red for a range (C1 to c100) if the corresponding cell b in each row is not equal to 'abc'. It will start from B1 and if B1<> abc highlights values > 45 min, moves to next row, it won't highlight if the cell b is abc and moves to next. Preferably I would need a do loop while cell b<> abc but I know it can be written with for loop as well.
I would appreciate your help
Sincerely,
S.A

 

[Joe4]

If you record a Macro applying Conditional Formatting, you get code that looks like this:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("C1:C28").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND($B1<>""abc"",$C1>(0.75/24))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub

Applying the method I gave in my last post, we can dynamically find the last row, so it will work on any number of rows of data dynamically, like this:

Sub Macro2()
'
' Macro1 Macro
'
    Dim lr As Long
    
'   Find last row in column C with data
    lr = Cells(Rows.Count, "C").End(xlUp).Row

    Range("C1:C" & lr).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND($B1<>""abc"",$C1>(0.75/24))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub

If you wanted to make it even more dynamic, you could have the VBA code prompt the user to enter the value they wish to avoid at run-time, like this:

Sub Macro3()
'
' Macro1 Macro
'
    Dim lr As Long
    Dim str As String
    
'   Prompt for value to avoid
    str = InputBox("What value do you want to avoid?")

'   Find last row in column C with data
    lr = Cells(Rows.Count, "C").End(xlUp).Row

    Range("C1:C" & lr).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND($B1<>" & Chr(34) & str & Chr(34) & ",$C1>(0.75/24))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub

So I think that should do what you want, yes?

Note that we could make the code a little shorter and faster by eliminating/combining the "Select/Selection" lines, but I purposely did not do that just to show you how you can turn recorded code into dynamic code with just a few small edits.