Search code examples
vbaexcelexcel-2010excel-2013

VBA - Conditional formatting: Excel 2010 won't work, Excel 2013 will

I recently wrote a piece of code that detects (but doesn't select) a Range which must be conditional formatted and then calls a subroutine that does the job.

' Format
For Each ws In Results.Sheets
    Format_em_all ws.Range(ws.Cells(15, 2), ws.Cells(15 + UBound(FreqToCompare), WrittenCells))
Next ws

Where ws is declared as Worksheet, Results as Workbook, FreqToCompare is an array with no possibility of being empty and WrittenCellsis an Integer not = 0.

The Subroutine called:

Sub Format_em_all(RangeToFormat As Range)

    Select Case RangeToFormat.Parent.Name

    Case "lol"
        RangeToFormat.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=2.9"
        RangeToFormat.FormatConditions(1).Interior.ColorIndex = 3
        RangeToFormat.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=2.00001", Formula2:="=2.9"
        RangeToFormat.FormatConditions(2).Font.ColorIndex = 3

    Case "rofl"
        RangeToFormat.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, Formula1:="=-4", Formula2:="=4"
        RangeToFormat.FormatConditions(1).Interior.ColorIndex = 3

    End Select

End Sub

I executed the code on my computer with Excel 2013 and all went smooth. 10 mins ago comes a colleague and says that my macro doesn't work... we go togheter to check on his computer with Excel 2010... and yeah, it doesn't work. It gets stuck at the first line after Case "lol" with a "Runtime error "5" - Invalid procedure call or argument". I tried to ? RangeToFormat.Address and it shows the correct range address... What is wrong with it?

Solution

  • Well the credit for this answer all goes to Axel Richter:

    Sub Format_em_all(RangeToFormat As Range)

Select Case RangeToFormat.Parent.Name

Case "lol"
    RangeToFormat.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=2.9
    RangeToFormat.FormatConditions(1).Interior.ColorIndex = 3
    RangeToFormat.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:=2.00001, Formula2:=2.9
    RangeToFormat.FormatConditions(2).Font.ColorIndex = 3

Case "rofl"
    RangeToFormat.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, Formula1:=-4, Formula2:=4
    RangeToFormat.FormatConditions(1).Interior.ColorIndex = 3

End Select

End Sub

    As you can see, the formulas are not formulas anymore, but integers! That works flawlessly