PDA

View Full Version : [SOLVED] Conditional Format Cells



Paleo
05-07-2005, 05:59 PM
I have a list of values in cells I2:I10, and I need to find which are the 3 smaller values and then color then this way:

Smallest = ColorIndex = 50
Secound Smallest: ColorIndex = 6
Third: ColorIndex = 7

I know how to color cells but just dont know how to determine then. Any help?:dunno

Bob Phillips
05-07-2005, 06:33 PM
I assume that you are using conditional formatting (just don't get what colorindex has to do with it), so if so, you just use 3 formulas of


=I2=SMALL($I$2:$I$10,1)
=I2=SMALL($I$2:$I$10,2)
=I2=SMALL($I$2:$I$10,3)

brettdj
05-07-2005, 07:58 PM
Paleo,

Did you want to limit the formatting to the smallest three cells and then pick the first three cells if there are multiple cells with the same smallest values?

The formulae above will format more than 3 cells if there are mutiple small values

Cheers

Dave

Paleo
05-07-2005, 09:47 PM
Hi guys,

yes xld it worked just fine thanks, but I need it in VBA because I need to execute several other operations after determining those 3 values.

Hi Dave,

thats not an issue for me. If there is 2 smallest cells its ok for me.

Bob Phillips
05-08-2005, 02:54 AM
yes xld it worked just fine thanks, but I need it in VBA because I need to execute several other operations after determining those 3 values.

I thought that might be the case after I posted the remark about colorindex.



Public Sub CFRange()
Dim rng As Range
Dim oFormat As FormatCondition
Set rng = Range("I2:I10")
With rng
.FormatConditions.Delete
Set oFormat = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=I2=SMALL($I$2:$I$10,1)")
oFormat.Interior.ColorIndex = 50
Set oFormat = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=I2=SMALL($I$2:$I$10,2)")
oFormat.Interior.ColorIndex = 6
Set oFormat = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=I2=SMALL($I$2:$I$10,3)")
oFormat.Interior.ColorIndex = 7
End With
End Sub

Paleo
05-08-2005, 04:36 PM
Hi xld,

your code seems to be fine, but it didnt work for me. When I run it nothing happens, not even an error. Any suggestions?

mdmackillop
05-08-2005, 05:06 PM
Hi Calos,
When I run it I get this in the formulas boxes; the colours are also applied, but no result shows


=K65522=SMALL($I$2:$I$10,2)

mdmackillop
05-08-2005, 05:10 PM
Strange things Carlos.
I re-entered the I2 values in the code, and came up with =M...
I did it again and the now code works.

Paleo
05-08-2005, 05:17 PM
Hi Malcolm,

yes if I put "=I2=SMALL($I$2:$I$10,2)" in a cell it returns me "true" or "false", according to the case, and it works fine.

The problem is when I try to run it as a function. I have adapted xld code to this:


Private Sub CommandButton2_Click()
Dim i As Long
For i = 2 To 10
If Range("I" & i).Formula = "=I" & i & "=SMALL($I$2:$I$10,1)" Then
Range("D" & i & ":I" & i).Interior.ColorIndex = 50
ElseIf Range("I" & i).Formula = "=I" & i & "=SMALL($I$2:$I$10,2)" Then
Range("D" & i & ":I" & i).Interior.ColorIndex = 6
ElseIf Range("I" & i).Formula = "=I" & i & "=SMALL($I$2:$I$10,3)" Then
Range("D" & i & ":I" & i).Interior.ColorIndex = 7
End If
Next
End Sub


What I wanna do is to color cells from columns "D" to "I" according to the result in column "I".

Dont know if my approach is the best for this need, actually I think it isnt, so of course I accept any upgrades to it.

mdmackillop
05-08-2005, 05:22 PM
Hi Carlos,
I was not entering it in the cells, but in the code!!! and rerunning the code gave the differing results.

mdmackillop
05-08-2005, 05:41 PM
No more time tonight, but I would also look at the Rank function.

Paleo
05-08-2005, 09:59 PM
Ok, using xld solution, I would adapt it to:


Public Sub CFRange()
Dim rng As Range
Dim oFormat As FormatCondition
Set rng = Range("D2:I10")
With rng
.FormatConditions.Delete
Set oFormat = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=I2=SMALL($I$2:$I$10,1)")
oFormat.Interior.ColorIndex = 50
Set oFormat = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=I2=SMALL($I$2:$I$10,2)")
oFormat.Interior.ColorIndex = 6
Set oFormat = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=I2=SMALL($I$2:$I$10,3)")
oFormat.Interior.ColorIndex = 7
End With
End Sub


I think this would be the best solution till now.

johnske
05-08-2005, 11:15 PM
Hi Carlos,

With conditional formatting, you have to select the cells to apply the formatting, try this variation on what you have:


Option Explicit
Sub FormatRange()
Dim Format As FormatCondition
[I2:I10].Select
With Selection
.FormatConditions.Delete
Set Format = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=I2=SMALL($I$2:$I$10,1)")
Format.Interior.ColorIndex = 50
Set Format = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=I2=SMALL($I$2:$I$10,2)")
Format.Interior.ColorIndex = 6
Set Format = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=I2=SMALL($I$2:$I$10,3)")
Format.Interior.ColorIndex = 7
End With
[H2].Activate
End Sub


Note that the formula used may not give you exactly what you want... e.g. If you have (say) three or more of the smallest values they will all colour ok but the second and third lowest values will not colour at all...

Regards,
John

Bob Phillips
05-09-2005, 01:07 AM
Note that the formula used may not give you exactly what you want... e.g. If you have (say) three or more of the smallest values they will all colour ok but the second and third lowest values will not colour at all...

Already established as not an issue.

Bob Phillips
05-09-2005, 01:22 AM
I think this would be the best solution till now.

Sorry Paleo, inadequate testing. I had I2 selected on my tests.

The problem is caused by the realative adjustment of formulas by Excel, a very neat function, but it can catch you out sometimes.:doh:. This means that if you are in cell H3 when the conditional format is set, as that is one row and one column off the relative address in the formula, I2, it will adjust that realative address by one row and one column, and give J1. Thus the conditional formatting will be testing the incorrect cells.

The solution is not to select the range as suggestsed elsewhere (always cast doubt on anyone who says you need to select in VBA:)), but to allow for this adjustment in the code by using the activecell.



Public Sub CFRange()
Dim rng As Range
Dim oFormat As FormatCondition
Set rng = Range("I2:I10")
With rng
.FormatConditions.Delete
Set oFormat = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=" & ActiveCell.Address(False, False) & "=SMALL($I$2:$I$10,1)")
oFormat.Interior.ColorIndex = 50
Set oFormat = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=" & ActiveCell.Address(False, False) & "=SMALL($I$2:$I$10,2)")
oFormat.Interior.ColorIndex = 6
Set oFormat = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=" & ActiveCell.Address(False, False) & "=SMALL($I$2:$I$10,3)")
oFormat.Interior.ColorIndex = 7
End With
End Sub

johnske
05-09-2005, 01:55 AM
The solution is not to select the range as suggestsed elsewhere (always cast doubt on anyone who says you need to select in VBA:))


Absolutely true - I'd be the first to agree with that. We all occasionally jump in too quick and say something without really thinking it through. Don't we? :devil:

Bob Phillips
05-09-2005, 05:18 AM
"The only reason we're put on this earth is to help each other". ( Dr. Fred Hollows (http://www.abc.net.au/btn/australians/hollows.htm))

I love the way a program on Australians features a New Zealander :wot (another one to delete?).

Paleo
05-09-2005, 05:25 AM
Hi guys,

I get an error here:


Set oFormat = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=" & ActiveCell.Address(False, False) & "=SMALL($I$2:$I$10,1)")


Error = Invalid Argument or Call

Any suggestions?

Bob Phillips
05-09-2005, 05:33 AM
Error = Invalid Argument or Call

Carlos,

I have just re-tried it and it works fine for me :wot

Is there anything 'unusual' in what you are doing? Is that sheet active when you run it? What happens if you type
?Activecell.Address
in the VBE immediate window?

Can you post the spreadsheet?

Paleo
05-09-2005, 10:43 AM
Hi xld,

if I ask for activecell it returns me $C$13. I am running this macro through a button click.

Sure the sheet is active and no there isnt anything unusual in what I am doing.

As I am using Excel 2003 in portuguese and there are some issues in its translation I will test it a little bit more.

Paleo
05-09-2005, 10:58 AM
Lets try to modify the approach. How could I do this?

1) Use the excel formula SMALL(I2:I10,1) from VBE;
2) Find the cell containing the value from the upper expression;
3) Determine on which cell it is.

Number 3 is the only one I surely know.

Bob Phillips
05-09-2005, 11:01 AM
Carlos,

Just a thought, as I thought VBA always used English, but give this a try


Set oFormat = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=" & ActiveCell.Address(Falso, Falso) & "=SMALL($I$2:$I$10,1)")

Bob Phillips
05-09-2005, 11:06 AM
... or is it the SMALL






Set oFormat = .FormatConditions.Add(Type:=xlExpression, _

Formula1:="=" & ActiveCell.Address(False False & "=MENOR:$I$10,1)")

Bob Phillips
05-09-2005, 11:08 AM
Lets try to modify the approach. How could I do this?

1) Use the excel formula SMALL(I2:I10,1) from VBE;
2) Find the cell containing the value from the upper expression;
3) Determine on which cell it is.

Number 3 is the only one I surely know.
Not sure what you are saying, are you trying to get the cell address of the cell that has the smallest? If so, what if there are 2,3, ...?

Paleo
05-09-2005, 11:13 AM
Hi xld,

looks like the error is on the function.

If I put "=MENOR(I2:I10;1)" on a cell it returns me "2600", what is accurate.

But if I put in VBE this:


Range("J2").Select
ActiveCell.Formula = "=menor(I2:I10;1)"


I got an error 1004 on:


ActiveCell.Formula = "=menor(I2:I10;1)"


While:


Range("J2").Select
ActiveCell.Formula = "=small(I2:I10,1)"


Works fine!

So the error seems to be here:


Set oFormat = .FormatConditions.Add(Type:=xlExpression, _
Formula1:=


or here:



rng = Range("I2:I10")

Bob Phillips
05-09-2005, 12:29 PM
Carlos,

Working blind here as I can't see what you see, but let's keep persevering.

Try this:



Set oFormat = .FormatConditions.Add(xlExpression, _
"=" & ActiveCell.Address(False, False) & "=SMALL($I$2:$I$10,1)")


Good debugging BTW!

Paleo
05-09-2005, 09:37 PM
Hi guys,

well problem solved using this code:



Private Sub CommandButton2_Click()
Dim i As Long
For i = 2 To 10
Range("J" & i).Formula = "=I" & i & "=small(I2:I10,1)"
Range("K" & i).Formula = "=I" & i & "=small(I2:I10,2)"
Range("L" & i).Formula = "=I" & i & "=small(I2:I10,3)"
If Range("J" & i) = True Then
With Range("D" & i & ":I" & i)
.Font.ColorIndex = 50
.Font.Bold = True
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
End With
ElseIf Range("K" & i) = True Then
With Range("D" & i & ":I" & i)
.Font.ColorIndex = 6
.Font.Bold = True
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
End With
ElseIf Range("L" & i) = True Then
With Range("D" & i & ":I" & i)
.Font.ColorIndex = 7
.Font.Bold = True
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
End With
End If
Next
Columns("J:L").EntireColumn.Delete
End Sub


Of course this is not the best code and can be improved, but at least solved my problem.

If anyone have any idea to make it better it will be very appreciated!

mdmackillop
05-10-2005, 12:46 AM
Hi Carlos,
Here's a ranking solution. Maybe Small can be used in the same fashion?


Sub RankIt()
For i = 2 To 10
test = Evaluate("RANK(I" & i & ",I$2:I$11,-1)")
With Range("I" & i).Offset(0, -4).Range("A1:C1").Interior
Select Case test
Case Is = 1
.ColorIndex = 6
Case Is = 2
.ColorIndex = 7
Case Is = 3
.ColorIndex = 8
End Select
End With
Next
End Sub

Paleo
05-10-2005, 06:22 AM
Hi Malcolm,

very interesting approach. I tried it but I have a problem to implement it.

The last atribute ("-1") out of the rank function is intended to tell MS Excel in which order the data is distributed where 0 means in a descending order and any other number means in an ascending order. Well my data has no order at all and then the function gets lost. If I could adjust it it would be great, but I didnt acomplish that yet.

Any ideas on how to adjust it without ordering my data?

Paleo
05-10-2005, 06:33 AM
Hi Malcolm,

yes, actually you made it. With your suggestion I was able to make the code better, thanks.

Here is my final code (till the next great suggestion :thumb )



Private Sub CommandButton2_Click()
Dim i As Long
Dim test As Long, test1 As Long, test2 As Long
test = Evaluate("=small(I2:I10,1)")
test1 = Evaluate("=small(I2:I10,2)")
test2 = Evaluate("=small(I2:I10,3)")
For i = 2 To 10
With Range("D" & i & ":I" & i)
Select Case Range("I" & i)
Case Is = test
.Font.ColorIndex = 50
.Font.Bold = True
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
Case Is = test1
.Font.ColorIndex = 6
.Font.Bold = True
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
Case Is = test2
.Font.ColorIndex = 7
.Font.Bold = True
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
End Select
End With
Next
End Sub

mdmackillop
05-10-2005, 01:47 PM
Hi Carlos,
The -1 in the ranking determines the ranking order, but the data does not require to be sorted.

mdmackillop
05-10-2005, 01:53 PM
In the initial code, maybe you did not spot the deliberate error!

Sub RankIt()
For i = 2 To 10
test = Evaluate("RANK(I" & i & ",I$2:I$11,-1)")

Paleo
05-10-2005, 02:01 PM
In the initial code, maybe you did not spot the deliberate error!
Sub RankIt()
For i = 2 To 10
test = Evaluate("RANK(I" & i & ",I$2:I$11,-1)")


Yes, I saw it but I didnt think it was a deliberate error, I have considered it as a typing error :yes .

But, what do you think on my final solution? Can it be approved even more?:dunno

mdmackillop
05-10-2005, 02:05 PM
I still believe the ranking should work. Did you see my Post No. 31? I'll have another look at Small, which is new to me.

mdmackillop
05-10-2005, 03:01 PM
Less repetion, but a bit convoluted::think:


Option Explicit
Private Sub CommandButton2_Click()
Dim i As Long, j As Long
Dim cel
Dim MyRange As Range
Set MyRange = Range("I2:I10")
Dim MyTest(4)
For i = 1 To 3
MyTest(i) = Evaluate("=small(I2:I10," & i & ")")
Debug.Print MyTest(i)
Next
With MyRange
For Each cel In MyRange
With cel
For j = 1 To 3
If cel = MyTest(j) Then
If j = 1 Then .Font.ColorIndex = 50
If j = 2 Then .Font.ColorIndex = 7
If j = 3 Then .Font.ColorIndex = 6
.Font.Bold = True
With .Offset(0, -6).Range("A1:G1")
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
End With
End If
Next
End With
Next
End With
End Sub

Paleo
05-10-2005, 09:03 PM
Gr8 Malcolm,

its getting better. I made some little changes and now it looks like this:


Private Sub CommandButton2_Click()
Dim i As Long
Dim test As Long, test1 As Long, test2 As Long
test = Evaluate("=small(I2:I10,1)")
test1 = Evaluate("=small(I2:I10,2)")
test2 = Evaluate("=small(I2:I10,3)")
For i = 2 To 10
With Range("D" & i & ":I" & i)
Select Case Range("I" & i)
Case Is = test
.Font.ColorIndex = 50
Fundo (i)
Case Is = test1
.Font.ColorIndex = 6
Fundo (i)
Case Is = test2
.Font.ColorIndex = 7
Fundo (i)
End Select
End With
Next
End Sub

Sub Fundo(i As Long)
With Range("D" & i & ":I" & i)
.Font.Bold = True
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
End With
End Sub


So what do you think? Can we make it better? I will test the rank approach again, I promise.

mdmackillop
05-11-2005, 12:29 AM
Getting there; soon be as good as ranking!



Private Sub CommandButton2_Click()
Dim i As Long
For i = 2 To 10
With Range("I" & i)
Select Case Range("I" & i)
Case Is = Evaluate("=small(I2:I10,1)")
.Font.ColorIndex = 50
Fundo (i)
Case Is = Evaluate("=small(I2:I10,2)")
.Font.ColorIndex = 6
Fundo (i)
Case Is = Evaluate("=small(I2:I10,3)")
.Font.ColorIndex = 7
Fundo (i)
End Select
End With
Next
End Sub

Paleo
05-11-2005, 05:46 AM
Wow Malcolm,

yes, its getting better and better, only a little adjustment:



Private Sub CommandButton2_Click()
Dim i As Long
For i = 2 To 10
With Range("D" & i & ":I" & i)
Select Case Range("I" & i)
Case Is = Evaluate("=small(I2:I10,1)")
.Font.ColorIndex = 50
Fundo (i)
Case Is = Evaluate("=small(I2:I10,2)")
.Font.ColorIndex = 6
Fundo (i)
Case Is = Evaluate("=small(I2:I10,3)")
.Font.ColorIndex = 7
Fundo (i)
End Select
End With
Next
End Sub

mdmackillop
05-11-2005, 07:37 AM
The D here doesn't seem to serve any purpose

With Range("D" & i & ":I" & i)

mdmackillop
05-11-2005, 02:17 PM
Getting a bit contrived now!



Private Sub CommandButton2_Click()
Dim i As Long
Dim Test As String
Test = "=Small(I2:I10,"
For i = 2 To 10
With Range("I" & i)
Select Case .Value
Case Is = Evaluate(Test & 1 & ")")
.Font.ColorIndex = 50
Fundo (i)
Case Is = Evaluate(Test & 2 & ")")
.Font.ColorIndex = 6
Fundo (i)
Case Is = Evaluate(Test & 3 & ")")
.Font.ColorIndex = 7
Fundo (i)
End Select
End With
Next
End Sub

Paleo
05-11-2005, 09:06 PM
The D here doesn't seem to serve any purpose
With Range("D" & i & ":I" & i)


Hi Malcolm,

yes it has a purpose. I want to color all cells from column "D" to column ""I".

Paleo
05-11-2005, 09:09 PM
Getting a bit contrived now!


Private Sub CommandButton2_Click()
Dim i As Long
Dim Test As String
Test = "=Small(I2:I10,"
For i = 2 To 10
With Range("I" & i)
Select Case .Value
Case Is = Evaluate(Test & 1 & ")")
.Font.ColorIndex = 50
Fundo (i)
Case Is = Evaluate(Test & 2 & ")")
.Font.ColorIndex = 6
Fundo (i)
Case Is = Evaluate(Test & 3 & ")")
.Font.ColorIndex = 7
Fundo (i)
End Select
End With
Next
End Sub



Gee sorry, but its a little confused for my brains, I rather my last version, also there is an error for the purpose intended: it must color cells from column "D to column "I" and not only from column "I" as this code does.

mdmackillop
05-12-2005, 12:00 AM
Hi Carlos,
I agree the previous post is better, and I would never abbreviate as shown.
Regarding the omission of the D range, with me it does colour those cells; its the Fundo function which is doing the colouring, and only the i is being passed to it, the D is already in the Fundo code.

Paleo
05-12-2005, 06:41 AM
Hi Malcolm,

nope the Fundo code sets interior color and the font to bold, but we still need that D to set the font color.

Anyway, I just had an idea. We could pass the i and another attribute to Fundo code telling which color to set for the font. Something like this:


Private Sub CommandButton2_Click()
Dim i As Long
Dim Test As String
Test = "=Small(I2:I10,"
For i = 2 To 10
With Range("I" & i)
Select Case .Value
Case Is = Evaluate(Test & 1 & ")")
Fundo (i, 50)
Case Is = Evaluate(Test & 2 & ")")
Fundo (i, 6)
Case Is = Evaluate(Test & 3 & ")")
Fundo (i, 7)
End Select
End With
Next
End Sub


This way we are shortening it a little bit more and at Fundo code we set the font color. Gee, we are making it even better than I though we would. This is great, thanks for the dedication Malcolm!

Bob Phillips
05-12-2005, 06:57 AM
Gee, we are making it even better than I though we would.

If you really want to make it good, you would get rid of the unnecessary Evaluate.



Private Sub CommandButton2_Click()
Dim i As Long
Dim rng As Range
Set rng = Range("I2:I10")
For i = 2 To 10
With Range("I" & i)
Select Case .Value
Case Is = Application.Small(rng, 1)
Fundo i, 50
Case Is = Application.Small(rng, 2)
Fundo i, 6
Case Is = Application.Small(rng, 3)
Fundo i, 7
End Select
End With
Next
End Sub

Paleo
05-13-2005, 06:59 AM
Gee xld,

thats why I love this site. When I think a code is great, some other member come along and make it even better!

Many thanks!

Bob Phillips
05-13-2005, 08:36 AM
thats why I love this site. When I think a code is great, some other member come along and make it even better!

Just to elaborate, evaluate is a pretty inefficent solution, and should only be used as a last resort IMO. I know I first suggested it to you, but that was to resolve an array formula or somethin IIRC.

Using a worksheetfunction is preferable if possible, you won't get much more efficient than that.

Paleo
05-14-2005, 04:43 PM
Gee, sorry but now you mixed my mind. Do you mean its better to use that solution in the worksheet instead of VBA?