VBA Code to highlight highest valued cell in yellow

[Cquake]

On the following table below I would like to have the highest valued number to highlight with each roll. I would like to code in in VBA. I have looked at different sites but so far have had no luck finding what I need. Thanks for any help one can give me.

Momentum Roll (Step 1)
Offense	82
Defense	63
Momentum	49

 

[maabadi]

Try this:

Sub HighestValue()
Dim i As Long, j As Long, Lr As Long, M As Double
Lr = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:B" & Lr).Interior.Color = 16777215
M = Range("B2:B" & Lr).Find(Application.WorksheetFunction.Max(Range("B2:B" & Lr))).Row
Range("A" & M & ":B" & M).Interior.Color = vbYellow
End Sub

 

[Peter_SSs]

I would like to code in in VBA.

Is there a particular reason that you wouldn't just apply standard Conditional Formatting and leave it there for each roll?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Cquake]

Try this:

Sub HighestValue()
Dim i As Long, j As Long, Lr As Long, M As Double
Lr = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:B" & Lr).Interior.Color = 16777215
M = Range("B2:B" & Lr).Find(Application.WorksheetFunction.Max(Range("B2:B" & Lr))).Row
Range("A" & M & ":B" & M).Interior.Color = vbYellow
End Sub

Thanks for the help. It worked perfectly.

 

[Cquake]

Is there a particular reason that you wouldn't just apply standard Conditional Formatting and leave it there for each roll?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

To be honest when it comes to conditional formatting I know how to use it for certain things but wasn't sure what the right steps were to highlight the highest number. I mainly only have used it for situations for "equal to". Updated my info hopefully correctly.

 

[Peter_SSs]

Updated my info hopefully correctly.

Thanks for updating your profile.

.. wasn't sure what the right steps were to highlight the highest number.

Suppose the roll values are going in to column B, starting at row2.

1. Select B2 down to wherever the last roll value is or might ever be.
2. Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =AND(B2=MAX(B$2:B$10),B2<>"") -> Format... -> Fill tab -> Choose your colour -> OK -> OK.
   In that formula where I have 10, use the row number of the last cell selected in step 1 above

Once you have that set up it should highlight the largest value wherever it occurs in that range of cells (unless you have other vba that removes the existing CF)

21 08 22.xlsm
	B
1
2	2
3	5
4	6
5	4
6
7
8
9
10
CF Max

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B10	Expression	=AND(B2=MAX(B$2:B$10),B2<>"")	text	NO

 

[Cquake]

Thanks for updating your profile.


Suppose the roll values are going in to column B, starting at row2.

1. Select B2 down to wherever the last roll value is or might ever be.
2. Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =AND(B2=MAX(B$2:B$10),B2<>"") -> Format... -> Fill tab -> Choose your colour -> OK -> OK.
   In that formula where I have 10, use the row number of the last cell selected in step 1 above

Once you have that set up it should highlight the largest value wherever it occurs in that range of cells (unless you have other vba that removes the existing CF)

21 08 22.xlsm
	B
1
2	2
3	5
4	6
5	4
6
7
8
9
10
CF Max

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B10	Expression	=AND(B2=MAX(B$2:B$10),B2<>"")	text	NO

Thank you for that info.

 

[Peter_SSs]

You're welcome.