Conditional Format Cells

[Paleo]

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?

[Bob Phillips]

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]

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]

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]

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]

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]

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]

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]

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]

Hi Carlos,
I was not entering it in the cells, but in the code!!! and rerunning the code gave the differing results.

[mdmackillop]

No more time tonight, but I would also look at the Rank function.

[Paleo]

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]

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]

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]

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.. 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]

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?

[Bob Phillips]

"The only reason we're put on this earth is to help each other". ( Dr. Fred Hollows)

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

[Paleo]

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]

Error = Invalid Argument or Call

Carlos,

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

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]

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.