Using a Sub routine for a formatting macro (conditional formatting)

[JohnBarwise]

Hi,

I am trying to simplify a macro I've written that formats several ranges with the same conditional formatting. The version I have that works has the same code in that adds the conditional formatting in to the required range about a dozen times. I want to create a subroutine that adds the formatting to the required range, which I can then just call rather than include the whole of the formatting code again.

The code I've got so far is below, but when I run it it gets stuck on the first line of the "ConditionalFormatting" Sub routine, saying "Run-time error '1004': Method 'Range' of object '_Global' failed"!! I'm must not be using the Range variable (CFRange / CondFormRange) correctly, but can't find on any forum what I'm doing wrong. Is there anyone that knows where I'm going wrong!?

I'm using Excel 2010 and Windows XP btw.

Thanks - John.

Option Explicit





Sub FormatReport()


    Dim sWorkbook As Variant
    'Dim i As Integer
    
    Application.DefaultFilePath = ThisWorkbook.Sheets("Settings").Range("D3")
    ''D3 contains "C:\Temp\Reports" on the workbook that this module is in.
    sWorkbook = Application.GetOpenFilename("Excel Files (*.xls),*.xls", 3, "Format Cobra Reports", , True)
    
    Dim oWorkbook As Workbook
    Set oWorkbook = Workbooks.Open(sWorkbook(1))
    oWorkbook.Activate
    
    With oWorkbook
        .Activate
        
        'get descriptions from existing report
        Dim oReport As Worksheet
        Set oReport = .Sheets("Report")
        
        Dim BottomRow As Integer
        BottomRow = Range("A20000").End(xlUp).Row
        
        Dim CondFormRange As Range
        
        'Apply Conditional Formatting to SPI and CPI Ranges
            'Conditional Formatting for Cumulative Figures
            Set CondFormRange = Range(Cells(8, 9), Cells(BottomRow - 2, 10))
            Call ConditionalFormatting(CondFormRange)
            
            'Conditional Formatting for RB13 Figures
            Set CondFormRange = Range(Cells(8, 16), Cells(BottomRow - 2, 17))
            Call ConditionalFormatting(CondFormRange)
            
            'Conditional Formatting for In Period Figures
            Set CondFormRange = Range(Cells(8, 23), Cells(BottomRow - 2, 24))
            Call ConditionalFormatting(CondFormRange)
            
            ' etc. for several other ranges......
        
    End With
End Sub




Sub ConditionalFormatting(CFRange As Range)


            'Conditionally Format Cell background to Red if less than or equal to 0.9
            Range(CFRange).FormatConditions.Add Type:=xlExpression, Formula1:="=I8<=0.9"
            With Range(CFRange).FormatConditions(1)
                .Interior.Pattern = xlSolid
                .Interior.PatternColorIndex = xlAutomatic
                .Interior.Color = 255
                .Interior.TintAndShade = 0
                .Interior.PatternTintAndShade = 0
                .Font.ThemeColor = xlThemeColorDark1
                .Font.TintAndShade = 0
                .StopIfTrue = True
            End With




End Sub

 

[dmt32]

Hi,
see if this change to your code helps:

Sub ConditionalFormatting(ByVal CFRange As Range)


            'Conditionally Format Cell background to Red if less than or equal to 0.9
            CFRange.FormatConditions.Add Type:=xlExpression, Formula1:="=I8<=0.9"
            With CFRange.FormatConditions(1)
                .Interior.Pattern = xlSolid
                .Interior.PatternColorIndex = xlAutomatic
                .Interior.Color = 255
                .Interior.TintAndShade = 0
                .Interior.PatternTintAndShade = 0
                .Font.ThemeColor = xlThemeColorDark1
                .Font.TintAndShade = 0
                .StopIfTrue = True
            End With
End Sub

Dave

 

[JohnBarwise]

It worked!! Knew it would be something simple. Thanks very much Dave!

 

[dmt32]

It worked!! Knew it would be something simple. Thanks very much Dave!

welcome - many thanks for feedback
Dave