PDA

View Full Version : [SOLVED] find highest high and highlight it



asdzxc
08-19-2014, 11:14 PM
maximum = Application.WorksheetFunction.Max(Range("I11:I40"))
MsgBox maximum
the above code displaying highest value in a box, plse alter the code so that the highest value
will be hightlighted in red color.

mancubus
08-20-2014, 12:31 AM
you dont need vba for this.

conditional formatting - top/bottom rules - top 10 items
change spin button value 10 to 1
apply desired format

mancubus
08-20-2014, 01:12 AM
and this is a vba solution.



Dim rng As Range
Set rng = Range("I1:I" & Range("I" & Rows.Count).End(xlUp).Row)
rng.Find(Application.Max(rng)).Interior.Color = vbRed

asdzxc
08-20-2014, 03:17 AM
and this is a vba solution.



Dim rng As Range
Set rng = Range("I1:I" & Range("I" & Rows.Count).End(xlUp).Row)
rng.Find(Application.Max(rng)).Interior.Color = vbRed

ran macro, plse see attached S2

mancubus
08-20-2014, 03:58 AM
see attached file.
it seems when you copy paste the code double quotes (") are changed to some other character.

asdzxc
08-20-2014, 04:30 AM
Your macro is working but when pasted into my macro and retyped double quotes ("), same error happened

mancubus
08-20-2014, 04:47 AM
so why dont you upload your file after removing any sensitive data.

Bob Phillips
08-20-2014, 05:17 AM
But why are you wasting time with VBA when conditional formatting will do it for you, and be dynamic?

asdzxc
08-20-2014, 05:25 AM
file uploaded
below is code

Columns("A:B").Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Range("C3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]-R[-1]C[-1]"
Range("D3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>0,RC[-1],0)"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]<0,-RC[-2],0)"
Range("F11").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-8]C[-2]:RC[-2])/9"
Range("G11").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-8]C[-2]:RC[-2])/9"
Range("H11").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-1]"
Range("I11").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=0,100,100-(100/(1+RC[-1])))"
Range("C3:E3").Select
Selection.AutoFill Destination:=Range("C3:E35"), Type:=xlFillDefault
Range("C3:E35").Select
Range("F11:I11").Select
Selection.AutoFill Destination:=Range("F11:I35"), Type:=xlFillDefault
Range("F11:I35").Select
Columns("I:I").Select
Range("I6").Activate
With Selection.Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
Dim rng As Range
Set rng = Range("I1:I" & Range("I" & Rows.Count).End(xlUp).Row)
rng.Find(Application.Max(rng)).Interior.Color = vbRed
End With
End Sub

mancubus
08-20-2014, 07:21 AM
i may think find method fails because of the digits of the max value, but i am not sure.

delete last line and add below instead.



Range("I" & Application.Match(Application.Max(rng), rng, 0)).Interior.Color = vbRed



btw, repeat again, i would use conditional formatting.

asdzxc
08-20-2014, 06:18 PM
SOLVED

mancubus
08-20-2014, 11:10 PM
and avoid select.

use

Range("C3").FormulaR1C1 = "=RC[-1]-R[-1]C[-1]"

instead of

Range("C3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-R[-1]C[-1]"