Max value in a range

[Krb00]

Hi,
in the attached file I'd like the code to find the max value in a range and highlight it. I'd also like to highlight only the first occurrence, the value in B107.
What's wrong with the code?

Hope someone can help me with this
Thank you

Sub test()
Dim rngmax As Range
Dim maxT As Double
Dim maxrow As Integer
Set rngmax = Sheets(1).Range("B107:B150")
maxT = Round(Application.WorksheetFunction.Max(rngmax), 2)
maxrow = rngmax.Find(maxT, , xlValues).Row
Sheets(1).Range("B" & maxrow).Interior.ColorIndex = 45
End Sub

 

[DanteAmor]

If you only have one sheet, then, Try this:

Sub test()
  Dim rngmax As Range
  Dim maxT As Double
  Dim maxrow As Integer
  Set rngmax = Sheets(1).Range("B107:B150")
  maxT = WorksheetFunction.Max(rngmax)
  maxrow = rngmax.Find(maxT, , xlValues, xlWhole).Row
  Sheets(1).Range("B" & maxrow).Interior.ColorIndex = 45
End Sub

 

[Krb00]

Unfortunately, it keeps highlighting the second occurrence in B108.

 

[jdellasala]

I think Conditional Formatting would be an easier solution.

Book1
	C	D
1	Date/Time	Value
2	1/1/23 10:00:00	121.76
3	1/1/23 10:00:30	121.92
4	1/1/23 10:01:00	121.37
5	1/1/23 10:01:30	121.60
6	1/1/23 10:02:00	121.14
7	1/1/23 10:02:30	121.40
8	1/1/23 10:03:00	121.59
9	1/1/23 10:03:30	121.04
10	1/1/23 10:04:00	121.76
11	1/1/23 10:04:30	121.69
12	1/1/23 10:05:00	121.43
13	1/1/23 10:05:30	121.62
14	1/1/23 10:06:00	121.48
15	1/1/23 10:06:30	121.09
16	1/1/23 10:07:00	121.50
17	1/1/23 10:07:30	121.66
18	1/1/23 10:08:00	121.24
19	1/1/23 10:08:30	121.14
20	1/1/23 10:09:00	121.37
21	1/1/23 10:09:30	121.06
22	1/1/23 10:10:00	121.44
23	1/1/23 10:10:30	121.94
24	1/1/23 10:11:00	121.95
25	1/1/23 10:11:30	121.97
26	1/1/23 10:12:00	121.90
27	1/1/23 10:12:30	121.58
28	1/1/23 10:13:00	121.40
29	1/1/23 10:13:30	121.37
30	1/1/23 10:14:00	121.19
31	1/1/23 10:14:30	121.23
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:D31	Expression	=$D2=MAX($D$2:$D$31)	text	NO

Applies to:

=$C$2:$D$31

If you turned it into a table, the Conditional Formatting would grow as the table grows.

 

[Krb00]

Yes but I need it in vba, it's just a part of a larger code.

The code works fine only if I change the range to "B106:B150" but I can't figure out why.

 

[DanteAmor]

Unfortunately, it keeps highlighting the second occurrence in B108.

Try this. Add this parameter:

Sub test()
  Dim rngmax As Range
  Dim maxT As Double
  Dim maxrow As Integer
  Set rngmax = Sheets(1).Range("B107:B150")
  maxT = WorksheetFunction.Max(rngmax)
  maxrow = rngmax.Find(maxT, rngmax(rngmax.Rows.Count), xlValues, xlWhole).Row
  Sheets(1).Range("B" & maxrow).Interior.ColorIndex = 45
End Sub

The code works fine only if I change the range to "B106:B150" but I can't figure out why.

The Find method starts searching from the second cell of the range, it is a bug of the method, that is why it does not find the value in the first cell. And that's also why he finds the second cell. If no other equal value exists, then let's say the find method loops through all cells and returns to the first cell in the range (B107) then finds it and marks it.

And that's also why, when you changed the range to B106, then it starts searching in the second cell, that is, B107, then it finds it.
Another option is to tell the Find method to start at the last cell, this will make the find method search the second cell, that is, the first cell in the range.

 

[jdellasala]

Yes but I need it in vba, it's just a part of a larger code.

The code works fine only if I change the range to "B106:B150" but I can't figure out why.

When you copy the sample, you need to paste it into Cell C1, not A1. I make that mistake all the time, and hate when samples don't start at A1. Don't know why I did that.
You can change the ranges to cover the entire column, so that the Conditional Formatting Formula would be

=$B2=MAX($D:$D)

and the Applies To formula is

=$C:$D

Don't understand what would force the need for VBA.

 

[Fluff]

That formula will highlight the wrong row if applied to an entire column.

Don't understand what would force the need for VBA.

Irrelevant, the OP wants VBA.

 

[Krb00]

Try this. Add this parameter:

Sub test()
  Dim rngmax As Range
  Dim maxT As Double
  Dim maxrow As Integer
  Set rngmax = Sheets(1).Range("B107:B150")
  maxT = WorksheetFunction.Max(rngmax)
  maxrow = rngmax.Find(maxT, rngmax(rngmax.Rows.Count), xlValues, xlWhole).Row
  Sheets(1).Range("B" & maxrow).Interior.ColorIndex = 45
End Sub

The Find method starts searching from the second cell of the range, it is a bug of the method, that is why it does not find the value in the first cell. And that's also why he finds the second cell. If no other equal value exists, then let's say the find method loops through all cells and returns to the first cell in the range (B107) then finds it and marks it.

And that's also why, when you changed the range to B106, then it starts searching in the second cell, that is, B107, then it finds it.
Another option is to tell the Find method to start at the last cell, this will make the find method search the second cell, that is, the first cell in the range.

Now it works! Thank you!
I appreciated the explanation, I didn't know that.