[Webinar] Learn how to a build a cloud-first strategyRegister Now


MS Access 2010 change MS Excel cell background color using VBa

Posted on 2012-09-21
Medium Priority
Last Modified: 2012-09-25
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
Question by:newholyman
  • 3
  • 3

Expert Comment

ID: 38421659
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)
    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


Author Comment

ID: 38421745
I need to compare to  a date mm/dd/yy on form.

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

Expert Comment

ID: 38421752
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.
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.


Author Comment

ID: 38421761
In addition the column 12 not  column one

Accepted Solution

armchair_scouse earned 1500 total points
ID: 38421886
Yup...  my code is looking at column 12...  column L?

Have attached a working example for you to try.

Author Comment

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

Thank you

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question