Find largest number and manipulate cell

warpedone

Board Regular
Joined
May 1, 2002
Messages
139
I'm trying to find the largest of four values from b3:b6 and make the contents of the largest bold.

Right now I just compare the values of each cell with if statements and selectt the largest that way.

Is there a better way to do this?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
1) select b3:b6
2) go to format --> conditional formatting
3) change "cell value is" to "formula is"
4) enter =b3=max(b3:b6 )
5) click "format"
6) select bold
7) Press OK
8) Press OK

edit: Thought I was fast enough to sneak that correction in, Ken :)
 
Upvote 0
I need to do this in VBA.

The macro that runs the spreadsheet clears all cells in the workbook upon opening.
 
Upvote 0
Use Condtional Formatting. Highlight B3:B6, and, from the menu, choose Format>Conditional Formatting. In the Conditional Formatting dialog box, for condition 1, choose:

Cell Value Is....equal to....=MAX($B$3:$B$6)

Click the Format button and set the font to Bold. O.K., O.K.

EDIT RE: Using VBA on opening: Is the range of the numbers you want to conditionally format always going to be the same? If so, just make sure your opening macro doesn't clear the conditional formatting and the maximum number of the new data entry will still show in bold.
Book1
ABCD
37
44
59
63
Sheet2
 
Upvote 0
Right click on your sheet, go to "view code", and paste this:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">If</SPAN> Intersect(Target, Range("b3:b6")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">Else</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("B3:B6")
<SPAN style="color:#00007F">If</SPAN> cell = WorksheetFunction.Max(Range("b3:b6")) <SPAN style="color:#00007F">Then</SPAN>
cell.Font.FontStyle = "Bold"
Else: cell.Font.FontStyle = "regular"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Still not sure why you need to use code:-

=AND(B3<>"",B3=MAX($B$3:$B$6))

will bold it if there are values and ignore it if no values.

If you really need code then something like

Code:
Sub BoldMe()

Dim rng As Range
Dim cel As Range

    Set rng = ActiveWorkbook.Sheets("sheet1").Range("B3:B6")
    For Each cel In rng
        If cel.Value = Application.WorksheetFunction.Max(rng) Then
            cel.Font.Bold = True
        Else: cel.Font.Bold = False
        End If
    Next
End Sub
 
Upvote 0
That works but the cells are highlighted when they're all empty.

I'm posting 'results' in these cells and want to hightlight the largest AFTER the results are posted. Is there any way to not have the cells highlighted until the results are shown?
 
Upvote 0
Though thinking about it I guess it doesn't matter about catering for them all equalling the max (ie when they are all blank), so Oaktree's first formula with absolute reference for $B$3:$B$6 should do you.
 
Upvote 0
I meant the conditional formatting worked - I'll try the code.

I think I need the code (in lieu of a formula) because each time the workbook is opened, a macro wipes the contents from the previous user, thus, the formula in a cell would be erased.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top