CODEWITHSUNDEEP
excelvba
StormsEdge
StormsEdge

Reputation: 885

Comparing and highlighting time values

I am parsing and analyzing large data sets and attempting to highlight rows that share the same time of occurrence. This is a smaller piece of a larger macro and is currently the only piece not working (the rest works rather well!) I am trying to compare each time to the time before it and after it in order to capture all occurrences. If I compare just the time before, or just the time after, you can probably see how that would miss a lot of values. Code: 

'Hightlights trade structures via time

Do
DoEvents

row_number = row_number + 1

'row_color_tester is a proxy for testing trade time "structure principle"
row_color_tester = row_number
tradeTime = ActiveSheet.Range("B" & row_number)

If tradeTime = Range("B" & (row_color_tester + 1)) Or tradeTime = Range("B" & (row_color_tester - 1)) Then
    ActiveCell.EntireRow.Interior.ColorIndex = 8

End If
Loop Until row_number = lastRow + 1

Sample data: 

16:01:30
15:37:56
15:08:38
15:08:38
14:40:56
14:29:36
14:28:10
14:28:10
14:23:48
14:21:49
14:21:49
14:21:49
14:21:49
14:21:49
14:21:49
14:20:01
14:20:01
14:20:01
14:20:01
14:20:01
14:20:01
14:19:52
14:19:52
14:19:52
14:19:52
14:19:52
14:19:52
14:19:43
14:18:57
14:18:43
14:18:34
14:17:50
14:17:50
14:17:46
14:17:46
14:17:13
14:16:50
14:16:50
14:16:50
14:16:50
14:16:50
14:16:50
14:14:59
14:14:59
14:14:59
14:14:59
14:14:59
14:14:59
14:12:38
14:11:27
14:10:11
14:10:11
14:07:17
13:56:46
13:55:16
13:55:16
13:55:16
13:51:59
13:51:59
13:51:59
13:51:59
13:51:59
13:51:59
13:51:39
13:51:39
13:51:39
13:51:39
13:51:39
13:51:39
13:50:45
13:50:45
13:50:45
13:50:45
13:50:45
13:50:45
13:46:49
13:46:49
13:45:12
13:45:12
13:44:31
13:44:31
13:44:31
13:44:31
13:44:31
13:44:31
13:43:32
13:43:32
13:43:32
13:43:32
13:43:32
13:43:32
13:41:58
13:35:46
13:35:46
13:35:46
13:35:46
13:35:46
13:35:46
13:35:25
13:35:25
13:35:19
13:35:19
13:35:07
13:35:07
13:35:07
13:35:07
13:35:07
13:35:07
13:27:57
13:27:57
13:20:40
13:20:40

Upvotes: 0

Views: 55

Answers (3)

Scott Craner
Scott Craner

Reputation: 152505

As to why it is not working, times are tricky, they essentially are a formatted number. For example (0.598483796296296 to 0.598483796296300) will format to 14:21:49. This is why the way you have will sometimes work and sometimes not.

to fix it add .text to the end of the three ranges:

ActiveSheet.Range("B" & row_number).Text

This way you are only comparing the formatted text.

Also it is good practice not to use activecell. Use:

Rows(row_number).entirerow. Interior.colorindex = 8

Upvotes: 1

Demetri
Demetri

Reputation: 859

Assuming the times start in row 2 and column 1, a simple solution, can be attained with the following conditional formatting:

enter image description here

for the following range: enter image description here

Upvotes: 0

Jay
Jay

Reputation: 123

I have had to do something like this multiple times in the past. I always use the code found here:

http://www.cpearson.com/excel/findall.aspx

Upvotes: 0

Related Questions