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?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
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 :)
 

warpedone

Board Regular
Joined
May 1, 2002
Messages
139
I need to do this in VBA.

The macro that runs the spreadsheet clears all cells in the workbook upon opening.
 

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267
LOL - Touche Oaktree :)

Think you might want to make the A1:A10 absolute.
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053

ADVERTISEMENT

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
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
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>
 

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267

ADVERTISEMENT

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
 

warpedone

Board Regular
Joined
May 1, 2002
Messages
139
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?
 

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267
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.
 

warpedone

Board Regular
Joined
May 1, 2002
Messages
139
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

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
Top