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
newholymanAsked:
Who is Participating?
 
armchair_scouseConnect With a Mentor Commented:
Yup...  my code is looking at column 12...  column L?

Have attached a working example for you to try.
Database1.accdb
0
 
armchair_scouseCommented:
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

0
 
newholymanAuthor Commented:
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
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
armchair_scouseCommented:
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.
0
 
newholymanAuthor Commented:
In addition the column 12 not  column one
0
 
newholymanAuthor Commented:
I'm sorry, I only want to check column L  to a date field and only color coumn L

Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.