color largest values

  • Thread starter Thread starter pinmaster
  • Start date Start date
P

pinmaster

Hi, I have this formula to add the top 3 in a 6 cell range,
=SUMPRODUCT(--(LARGE(C34:C39,{1;2;3})))
I would like the formula to also color those top 3 values "blue" o
some other color, can this be done?...I can do it with conditiona
formating but I have over 1200 of those formulas!

Thanks
Jean-Guy
Canad
 
Jean-Guy,

You need conditional formatting.

Select C34:C39
Format>Conditional Formating
Change Condition 1 to Formula Is
Add formula of =C34=LARGE(C34:C39,1)
Click Format
Select the pattern tab
Pick your colour
Add a condition
Repeat with formula =C34=LARGE(C34:C39,2)
Repeated with formula =C34=LARGE(C34:C39,3)

You cannot combine the formula as in Frank's example, CF doesn't allow it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Jean-Guy

A cell can only contain a formula or
some data. It can't alter any other cell,
so conditional formatting is the way to
go. Depending on your data, a VBA
subroutine, which inserts the condition,
may save a substantial amount of time :-)
Is your data a block e.g. C34:AH39,
where each column (C34:C39, D34:D39
etc.) is calculated on its own?

BTW
There's no need to use SUMPRODUCT
and -- in this situation.
A simple
=SUM(LARGE(C34:C39,{1;2;3}))
will do.
 
Sorry if I sound ignorant but could you please tell me what the heck a
I suppose to get from this? :)

"Followup to newsgroup only please.
"pinmaster >" <<[email protected]> skrev i en
meddelelse
and to answer your question, Yes it is a block where every column i
calculated on its own, the first block start at C7 through AK12, and
have 35 blocks in all
 
If it's a block it's easier to write the code.
If you had 35 non-contiguous, you might
as well do the conditional formatting by hand :-)

This code will format the range with one blow.
It is assumed, that *NO* conditional formatting
has been applied to the range beforehand .

Insert the macro in a general module (<Alt><F11>,
Insert > Module), stay in the worksheet in question
and run the below macro.

The formula (Formula1) must be in the language of the
localized version of Excel, so you must alter the code,
if you have a non-English version.


Sub ThreeLargest()
'Leo Heuser, 25 Feb. 2004
Dim Checkrange As Range
Dim Counter As Long

Set Checkrange = ActiveSheet.Range("C7:AK12")

For Counter = 1 To Checkrange.Columns.Count
Checkrange.Columns(Counter).Select

With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=OR(" & .Cells(1, 1).Address(rowabsolute:=False,
_
columnabsolute:=False) & "=MAX(" & _
.Address & ")," & .Cells(1, 1).Address(rowabsolute:=False, _
columnabsolute:=False) & "=LARGE(" & _
.Address & ",2)," & .Cells(1, 1).Address(rowabsolute:=False,
_
columnabsolute:=False) & "=LARGE(" & .Address & ",3))"
.FormatConditions(1).Interior.ColorIndex = 8
End With
Next Counter
End Sub
 
In a more readable form:

Sub ThreeLargest()
'Leo Heuser, 25 Feb. 2004
Dim Checkrange As Range
Dim Counter As Long

Set Checkrange = ActiveSheet.Range("C7:AK12")

For Counter = 1 To Checkrange.Columns.Count
Checkrange.Columns(Counter).Select

With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=OR(" & .Cells(1, 1). _
Address(rowabsolute:=False, _
columnabsolute:=False) & "=MAX(" & _
.Address & ")," & .Cells(1, 1). _
Address(rowabsolute:=False, _
columnabsolute:=False) & "=LARGE(" & _
.Address & ",2)," & .Cells(1, 1). _
Address(rowabsolute:=False, _
columnabsolute:=False) & "=LARGE(" & .Address & ",3))"
.FormatConditions(1).Interior.ColorIndex = 8
End With
Next Counter
End Sub

LeoH
 
Hi Leo, I do have 35 non-contiguous blocks so I'm just going to forge
about the hole thing, it's way too much trouble for what it'
worth!...but thanks anyway!

Best regards
Jean-Guy Collette
Canad
 
Hi Jean-Guy

You're welcome.

If your blocks are C7:C12, D7:D12, E7:E12 etc.
the routine will work. That's because it works on
the collected block C7:AK12 one column at a time,
column 1 being C7:C12, column 2 being D7:D12,
and last column being AK7:AK12.

What I meant, when I said non-contiguous, was,
that if for instance your blocks were C7:C12,
E7:E12, H7:H12 etc. each single block had to be
written in the routine, so it might be just as "easy"
to do a manual conditional formatting. (Well, the
VBA way still may be faster :-)
 
Back
Top