Color a Range of Cells

[racer25]

Hi

I have been tinkering around trying to figure this out and to be quite honest got nowhere really.

I have code (recycled) in Access that loops through a query and generates a budget file for each person.

I would like to colour a range of cells based on the number of rows in the sheet, some people may have more clients than other. The cells have no data and the colouring is to lead people to which cells they need to enter data into.

The code I have so far is below, its a work in progress as I tidy up along the way, however this is the first part I have no idea on.

 Set ApXL = CreateObject("Excel.Application")

With ApXL
.Application.Visible = False
.UserControl = False
.Workbooks.Open strPath & strBrokerCode & " - FullIncomeRpt.xls"
              
' Formatting

    .Columns("A:A").ColumnWidth = 7.5
    .Columns("B:B").ColumnWidth = 33
    .Columns("C:G").Select
    .Selection.ColumnWidth = 12
    .Columns("C").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    .Columns("D").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    .Columns("E").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    .Columns("F").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    .Columns("G").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    .Range("I1").Formula = "Forecast Remainder 2010"
    .Range("J1").Formula = "Full Year Forecast 2010"
    .Range("L1").Formula = "Phasing Required"
    .Range("N1").Formula = "Budget 2011"
    .Range("P1").Formula = "Jan"
    .Range("Q1").Formula = "Feb"
    .Range("R1").Formula = "Mar"
    .Range("S1").Formula = "Apr"
    .Range("T1").Formula = "May"
    .Range("U1").Formula = "Jun"
    .Range("V1").Formula = "Jul"
    .Range("W1").Formula = "Aug"
    .Range("X1").Formula = "Sep"
    .Range("Y1").Formula = "Oct"
    .Range("Z1").Formula = "Nov"
    .Range("AA1").Formula = "Dec"
    .Range("AB1").Formula = "Err/Chk"
    .Rows("1:1").Select
    .Selection.WrapText = True
    .Selection.RowHeight = 50
    .Rows("2:4").Select
    .Selection.Insert shift:=xlDown
    .Selection.Interior.ColorIndex = xlNone
    .Selection.RowHeight = 15
    
    
    
    
    .Range("A1").Select
 
    
' Formula

Anything you can offer is appreciated.

Rob

[davesexcel]

Sorry Got carried away cleaning up, what's the question again, I am not sure if you are running this through excel or through access.

    'Set ApXL = CreateObject("Excel.Application")

    'With ApXL
    '.Application.Visible = False
    '.UserControl = False
    ''.Workbooks.Open strPath & strBrokerCode & " - FullIncomeRpt.xls"
    '
    '' Formatting
    With ActiveSheet
        .Columns("A:A").ColumnWidth = 7.5
        .Columns("B:B").ColumnWidth = 33
        .Columns("C:G").ColumnWidth = 12
        .Columns("C:G").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
        .Range("I1").Formula = "Forecast Remainder 2010"
        .Range("J1").Formula = "Full Year Forecast 2010"
        .Range("L1").Formula = "Phasing Required"
        .Range("N1").Formula = "Budget 2011"

        For i = 1 To 12 'creates months
            Cells(1, 15 + i) = Format(DateSerial(1, i, 1), "mmm")
        Next i

        .Range("AB1").Formula = "Err/Chk"
        With Rows("1:1")
            .WrapText = True
            .RowHeight = 50
        End With
        With Rows("2:4")
            .Insert shift:=xlDown
            .Interior.ColorIndex = xlNone
            .RowHeight = 15
        End With
    End With

[racer25]

Thanks for the tidy up - always good to learn a few things.

The code runs in access to produce a neatly formatted (allegedly) Excel file which attachs to an Outlook message and sends.

My "open" question is that each file contains a varying amount of rows and I want to color a range highlighting what people need to fill in.

In one file the range would be G4:G24 another one G4:G99 where G99 and G24 are the last row in the file with data.

My problem is how do I get the the G24 and G99?

Thanks again


Rob

[racer25]

Double Post -apologies

[davesexcel]

If the data is in column G then this will color column H

Sub Button1_Click()
    Dim r As Range
    Set r = Range("G4", Range("G65536").End(xlUp))
    r.Offset(0, 1).Interior.ColorIndex = 20
    
End Sub

or if you want column G colored

Sub Button1_Click()
    Dim r As Range
    Set r = Range("G4", Range("G65536").End(xlUp))
    r.Interior.ColorIndex = 20
    
End Sub

[racer25]

Thanks - I can't have a button click as this code runs in access - however I will playaround with your offset to see where that takes me.

I never open the files directly in Excel, its fully automated in Access ( or it will be when I have finished ).

250 Excel files

Thanks for quick response.

[davesexcel]

That's just the macro name.
Usually when you see submitted code with the sub button_click people start by making a button, assign macro,new. From there they can write the code, go back to the sheet click the button to test it.

[racer25]

What?
That's just the macro name.
Usually when you see submitted code with the sub button_click people start by making a button, assign macro,new. From there they can write the code, go back to the sheet click the button to test it, jeesh..

Apologies my misunderstanding

[racer25]

Thanks for your time and effort unfortunately the solution below did not work for 2nd file on.

Fist file was perfect 2nd failed as "Set r = Range("G4", Range("G65536").End(xlUp))" I did some googling and found that other experienced this issue when using access to create XL files.

I came up with an alternative method which given access knows how many records are in the resultant query send that variable through "RowCount" so I ended up with .Range("A2:A & RownCount)........

Thanks for again for your time and patience