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

 

[JGordon11]

Sub Highlight()
    With ActiveSheet.Range("$B$1:$C$1") 'need column headers or blank cells in $B$1:$C$1
        .AutoFilter Field:=1, Criteria1:="<>abc"
        .AutoFilter Field:=2, Criteria1:=">" & 0.75 / 24 '0.75/24 is 45 minutes in excel
        Range("C2:C101").Interior.Color = vbRed
        .AutoFilter
    End With
End Sub

Book2
	B	C
1	Column1	Column2
2	abc	2:24:00
3	def	0:14:24
4	abc	2:24:00
5	abc	0:14:24
6	abc	0:28:48
7	abc	1:40:48
8	abc	2:24:00
9	def	1:40:48
10	abc	0:57:36
11	abc	0:43:12
12	def	2:24:00
13	abc	0:14:24
14	abc	0:43:12
15	def	1:55:12
16	def	0:43:12
17	def	0:57:36
18	def	2:09:36
19	def	0:28:48
20	abc	0:57:36
21	abc	2:24:00
22	def	0:28:48
23	def	0:28:48
24	def	1:40:48
25	abc	1:26:24
26	def	1:12:00
27	abc	0:57:36
28	def	1:12:00
Sheet1

 

[Bamh1]

Sub Highlight()
    With ActiveSheet.Range("$B$1:$C$1") 'need column headers or blank cells in $B$1:$C$1
        .AutoFilter Field:=1, Criteria1:="<>abc"
        .AutoFilter Field:=2, Criteria1:=">" & 0.75 / 24 '0.75/24 is 45 minutes in excel
        Range("C2:C101").Interior.Color = vbRed
        .AutoFilter
    End With
End Sub

Book2
	B	C
1	Column1	Column2
2	abc	2:24:00
3	def	0:14:24
4	abc	2:24:00
5	abc	0:14:24
6	abc	0:28:48
7	abc	1:40:48
8	abc	2:24:00
9	def	1:40:48
10	abc	0:57:36
11	abc	0:43:12
12	def	2:24:00
13	abc	0:14:24
14	abc	0:43:12
15	def	1:55:12
16	def	0:43:12
17	def	0:57:36
18	def	2:09:36
19	def	0:28:48
20	abc	0:57:36
21	abc	2:24:00
22	def	0:28:48
23	def	0:28:48
24	def	1:40:48
25	abc	1:26:24
26	def	1:12:00
27	abc	0:57:36
28	def	1:12:00
Sheet1

Thank you for responding to this thread Gordon. I can see how using with is more efficient than looping in this case. One problem right now is that this code also highlights blank cells. I'm trying to add a third autofilter criteria for cell is not blank, but can't get the syntax right for is not blank or Isempty=false within the body of the autofilter Field statement, suggestions?

 

[JGordon11]

Sub Highlight()
    With ActiveSheet.Range("$B$1:$C$1") 'need column headers or blank cells in $B$1:$C$1
        .AutoFilter Field:=1, Criteria1:="<>abc", Operator:=xlAnd, Criteria2:="<>" & vbNullChar
        .AutoFilter Field:=2, Criteria1:=">" & 0.75 / 24 '0.75/24 is 45 minutes in excel
        Range("C2:C101").Interior.Color = vbRed
        .AutoFilter
    End With
End Sub

 

[Bamh1]

Thank you, this takes care of the blanks in the middle of data, but what about the situation that the range is defined as c2:c101, but the data ends at c20 free space under will be highlighted, I want to use this codefor data imports in excel where I don't know the range before hand, without telling the code every time. Also, how to expand the range to more than 2 columns, so you have your string values in column b, and columns c-e have time values. Do we have to add an autofilter Field per each column? Appreciate your help

 

[JEC]

Just use conditional formatting

 

[Bamh1]

Just use conditional formatting

So, here is the situation: every time the data is imported into excel, it has a different size. I want to reduce manual work to the minimum. With conditional formatting, I have to do it manually every time. Arguably, there is some manual work involved when running the VB code each time, but if I could have a code that works regardless of data size, I can save it as a macro and just run it with a push of a button

 

[Joe4]

So, here is the situation: every time the data is imported into excel, it has a different size. I want to reduce manual work to the minimum. With conditional formatting, I have to do it manually every time. Arguably, there is some manual work involved when running the VB code each time, but if I could have a code that works regardless of data size, I can save it as a macro and just run it with a push of a button

You CAN dynamically apply Conditional Formatting to any sized range with VBA, too!

You can actually get a lot of the VBA code you need for that by turning on your Macro Recorder, and recording yourself applying the Conditional Formatting.
Then you can stop the Recorder and edit your code to make it work on a dynamic range instead of a static one.

You can find the last row with data in column C like this:

Dim lr as Long
lr = Cells(Rows.Count, "C").End(xlUp).Row

 

[Bamh1]

You CAN dynamically apply Conditional Formatting to any sized range with VBA, too!

You can actually get a lot of the VBA code you need for that by turning on your Macro Recorder, and recording yourself applying the Conditional Formatting.
Then you can stop the Recorder and edit your code to make it work on a dynamic range instead of a static one.

You can find the last row with data in column C like this:

Dim lr as Long
lr = Cells(Rows.Count, "C").End(xlUp).Row

By recording, I cannot define the condition to avoid highlighting when the specific value 'abc' appears. It simply records where it is highlighted and where it is not highlighted, but appreciate sharing your code to find the end of data

 

[Bamh1]

By recording, I cannot define the condition to avoid highlighting when the specific value 'abc' appears. It simply records where it is highlighted and where it is not highlighted, but appreciate sharing your code to find the end of data

To explain this better, when you record, you highlight cells that are specific to that file. The positioning of the cells that need to be highlighted change in every data import.