Unable to format a cell containing maximum value

[tina95]

I have just started learning VBA. I want to find the maximum value from absolute values of cells and then format that cell by bolding it. I am able to find the max value of the absolute values of cells in the row by the code:

Maxx1= Application.WorksheetFunction.Max((Abs(Range("B11"))), (Abs(Range("C11"))), (Abs(Range("D11"))))...
However, if I do maxx1.select
or
maxx1.font.fontstyle = "Bold",
it gives an error "Object required".

Thus I am not able to format it.

Any inputs on this would be greatly appreciated.

 

[Peter_SSs]

I have just started learning VBA. I want to find the maximum value from absolute values of cells and then format that cell by bolding it. I am able to find the max value of the absolute values of cells in the row by the code:

Maxx1= Application.WorksheetFunction.Max((Abs(Range("B11"))), (Abs(Range("C11"))), (Abs(Range("D11"))))...
However, if I do maxx1.select
or
maxx1.font.fontstyle = "Bold",
it gives an error "Object required".

Thus I am not able to format it.

Any inputs on this would be greatly appreciated.

Tina

Welcome to the MrExcel board!

Do you need to use vba for this? Would this standard Conditional Formatting do? In my example, I selected B11:G11 and then applied the Conditional Formatting shown.

Excel Workbook
	B	C	D	E	F	G
11	25	-12	-32	8	31	4
CF MAX

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B11	1. / Formula is =ABS(B11)=MAX(ABS($B$11:$G$11))	Abc

 

[tina95]

Hi Peter,
Thank you very much for your reply. However, this is just part of a big project I am working on so VBA would be required for this.
Any inputs on this would be really appreciated.

 

[Peter_SSs]

Try this

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Max_Abs()<br>    <SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> myMax <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>, myMin <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>, Maxx1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> myRange = Range("B11", Cells(11, Columns.Count).End(xlToLeft))<br>    myMax = Abs(WorksheetFunction.Max(myRange))<br>    myMin = Abs(WorksheetFunction.Min(myRange))<br>    Maxx1 = IIf(myMax > myMin, myMax, myMin)<br>    myRange.Font.Bold = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> myRange<br>        <SPAN style="color:#00007F">If</SPAN> Abs(c.Value) = Maxx1 <SPAN style="color:#00007F">Then</SPAN><br>            c.Font.Bold = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[tina95]

Hello Peter,

This works. I thank you so much for your help.

Regards,
Natalie