asked on

MS Access 2010 change MS Excel cell background color using VBa

1. I need to open EXCEL work book.
2. compare column L to date
3 . If less than set back ground color to red
4. if greater than set back ground color green
5. if equal set background color to yellow

I would like this to use VBA code.

Using: MS Access 2010 and MS Excel

Andy Marshall

Try this:

Sub ColourDateCell(ByVal strPathAndFileName As String, ByVal strSheetNameToCheck As String, dtmDate As Date)

    Dim appExcel        As Excel.Application
    Dim wbk             As Excel.Workbook
    Dim wks             As Excel.Worksheet
    Dim rngLastCell     As Excel.Range
    Dim lngFirstCell    As Long
    Dim lngLastCell     As Long
    Dim lngLoop         As Long
    
    Set appExcel = New Excel.Application
    Set wbk = appExcel.Workbooks.Open(strPathAndFileName)
    Set wks = wbk.Worksheets(strSheetNameToCheck)
    
    wks.Activate
    Set rngLastCell = wks.Cells(wks.Rows.Count, 12).End(xlUp)
    lngLastCell = rngLastCell.Row            ' Get the row number of last cell
    lngFirstCell = 1                        ' Assuming first cell to be checked is in row 1
    wks.Cells(1, 1).Select                  ' Selct first cell on worksheet
    
    ' Loop through range of cells and colour accordingly
    For lngLoop = lngFirstCell To lngLastCell
        ' Is it a date?
        If IsDate(wks.Cells(lngLoop, 12)) Then
            Select Case wks.Cells(lngLoop, 12)
                Case Is > dtmDate
                    wks.Cells(lngLoop, 12).Interior.ColorIndex = 4     ' Green
                Case Is < dtmDate
                    wks.Cells(lngLoop, 12).Interior.ColorIndex = 3     ' Red
                Case dtmDate
                    wks.Cells(lngLoop, 12).Interior.ColorIndex = 6     ' Yellow
            End Select
        End If
    Next lngLoop

    Set wks = Nothing
    Set wbk = Nothing
    Set appExcel = Nothing


End Sub

Open in new window

newholyman

ASKER

I need to compare to a date mm/dd/yy on form.
thank

wrk_date =09/22/12
if wks.cells > wrk_date then
change color grenn
end if

Andy Marshall

No problem. The procedure I wrote accepts date as a parameter, so when you open your form in Access and select your date, pass the date you selected as a paramter to the procedure, along with the path/name of your workbook, and the name of the worksheet.

newholyman

ASKER

In addition the column 12 not column one

ASKER CERTIFIED SOLUTION

Andy Marshall

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

newholyman

ASKER

I'm sorry, I only want to check column L to a date field and only color coumn L

Thank you