David McAfee
davidmcafee at gmail.com
Fri Jan 27 15:01:52 CST 2012
I have this stored procedure that I export into an excel sheet.
I'd like to add two conditional formats that highlight column M (light) red
(pink?) if the value is <0.
The other format would color column N Yellow if the value >.2 (20%)
I could do the coloring, but the user wants the conditional formatting
instead in case they play with the sheet's numbers.
Does anyone know how to do this off the top of their head?
Existing code:
Private Sub cmdExpExcel_Click()
Dim strmySql As String, strOutPutFile As String, objXL As Object, row
As Integer
On Error GoTo cmdExpExcel_Click_Error
strmySql = ("EXEC RRMS.dbo.stpR6Payouts '" & Trim(Me.txtStart) & "','"
& Trim(Me.txtEnd) & "','" & Nz(Me.txtComp, "") & "'")
strOutPutFile = Me.Application.CurrentProject.Path & "\Type6Payouts.xls"
DoCmd.OutputTo acOutputStoredProcedure, strmySql, acFormatXLS,
strOutPutFile, False
'Format the sheet
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.Workbooks.Open strOutPutFile
.cells.EntireColumn.AutoFit
.Worksheets("EXEC RRMS.dbo.stpR6Payouts '12_").Name = "R6Payouts"
row = .CountA(.Worksheets("R6Payouts").Range("A:A"))
With .Worksheets("R6Payouts").Range(.cells(2, 13).cells(row, 13))
.Select
.FormatConditions.Delete
.FormatConditions.Add 'If cells in this range are <0 make the
cell's fill color light red
End With
With .Worksheets("R6Payouts").Range(.cells(2, 14).cells(row, 14))
'If cells in this range are >.2 make the cell's fill color
YELLOW
End With
' row = objXL.CountA(objXL.Worksheets("R6Payouts").Range("A:A"))
' With objXL.Worksheets("R6Payouts").Range(.Cells(1, 1), .Cells(row,
11))
' .Borders(7).LineStyle = 1 'xlEdgeLeft
' .Borders(8).LineStyle = 1 'xlEdgeTop
' .Borders(9).LineStyle = 1 'xlEdgeBottom
' .Borders(10).LineStyle = 1 'xlEdgeRight
' .Borders(11).LineStyle = 1 'xlInsideVertical
' .Borders(12).LineStyle = 1 'xlInsideHorizontal
' End With
End With
Set objXL = Nothing
On Error GoTo 0
Exit Sub
cmdExpExcel_Click_Error:
If Err.Number = 2302 Then
MsgBox "R6Payout cannot be exported. It is possible that you
currently have the file open", vbOKOnly, "Can't export data"
Else
MsgBox "Error " & Err.Number & " (" & Err.description & ") in
procedure cmdExpExcel_Click of VBA Document Form_frmType6PayOutHdr"
End If
End Sub