MDHowell
asked on
Conditional Format Duplicates in Excel from Access
Using Microsoft Access/VBA, I'm copying a recordset from an Access query into an Excel spreadsheet. I'd like to use conditional formatting on column N to check for duplicates and highlight the cells in red.
I'm having a hard time finding a solution for this. Any help is greatly appreciated!
I'm having a hard time finding a solution for this. Any help is greatly appreciated!
Function CDataAudit
Dim Rst As DAO.Recordset, i As Long
Dim ExlApp As Object, ExlBook As Object, ExlSheet As Object
On Error GoTo Function_Error
Dim strTemp As String: strTemp ='location of template
Dim strXLS As String: strXLS='location of output
Set ExlApp = CreateObject("Excel.Application")
Set ExlBook = ExlApp.Workbooks.Open(strTemp)
Set Rst = CurrentDb.OpenRecordset("qselDataAudit")
If Not Rst.EOF Then
Rst.MoveLast
i = Rst.RecordCount
End If
Rst.MoveFirst
With ExlBook
.ActiveSheet.Range("A2").CopyFromRecordset Rst
.Worksheets(1).Range("H2:H" & i).NumberFormat = "$#,##0.00"
.Worksheets(1).Range("J2:J" & i).NumberFormat = "$#,##0.00"
.Worksheets(1).Range("L2:L" & i).NumberFormat = "$#,##0.00"
End With
With ExlApp
.ActiveWorkbook.SaveAs (strXLS)
End With
Function_Exit:
If Not Rst Is Nothing Then
Rst.Close
Set Rst = Nothing
End If
If Not ExlSheet Is Nothing Then
Set ExlSheet = Nothing
End If
If Not ExlBook Is Nothing Then
Set ExlBook = Nothing
End If
If Not ExlApp Is Nothing Then
ExlApp.Quit
Set ExlApp = Nothing
End If
Exit Function
Function_Error:
MsgBox Error$
Resume Function_Exit
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Open in new window