Access VBA to add Conditional Formatting In Excel (From Access)

[Kody_Devl]

Hi All,

I want to add conditional formatting using VBA from Access inserted into my excel spreadsheet.
I am unable to find specific code example that inserts the rules correctly (my desired rules which are displayed the attachment above).



Here are some failed code attempts:
Can you help me?

With sh.Name


   'With Range("A2:A30").FormatConditions.Add(xlCellValue, xlGreater, "400")
   'With Range("I2:I30").FormatConditions.Add(xlCellValue, xlEqual, "R")
   'With Range("I2:I30").FormatConditions.Add(xlCellValue, xlEqual, "R")
   'With Range("I2:I30").FormatConditions.Add("$AF2", xlEqual, "R")
   'With Range("I2:I30").FormatConditions.Add("$AF2", xlEqual, "R")
   'With Range("I2:I30").FormatConditions.Add (Type:=xlExpression, Formula1: "=$AF2="R"")
   'With Range("I2:I30").FormatConditions.Add (Type: =xlexpression, Formula1:="=$AF2="R"")
   'With Range("I2:I30").FormatConditions.Add(Type:=xlExpression, Formula1:="=$G2=$D2")
   'With Range("I2:I30").FormatConditions.Add(Type:=xlExpression, Operator:=xlEqual, Formula1:="=$AF2="R"")
   'With Range("I2:I30").FormatConditions.Delete
   'With Range("I2:I30").FormatConditions.Add(Type:=xlExpression, Formula:="=$AF2=R"")
   With Range("I2:I30").FormatConditions.Add(xlExpression, "=$AF2=R")
   
   With .Interior
   .ColorIndex = 36
   End With

   End With
   End With

.



Many Thanks

Kody_Devl

[Gicu]

Here is some guidance on this:

https://www.wallstreetmojo.com/vba-c...al-formatting/
https://bettersolutions.com/excel/co...g/vba-code.htm

Cheers,

[ssanfu]

Part of the problem with the VBA code is there are 14 WITH statements, but only 3 END WITH statements. For each "With", there MUST be an "End With" (just like Sub...End Sub)

Maybe this will help you
I use a button ("cmdExportData") to export the data to an Excel workbook

Private Sub cmdExportData_Click()
   '
   'other code to export data to Excel workbook
   '
   'now edit the newly created workbook
   Call EditVendorWkSht(strSaveFileName, "Statement Monthly Summary", Me.cboMonth & " " & Me.cboYear & " Hours")
End Sub

The call runs the code to add two header rows and adds the Vendor Name and the Date

Sub EditVendorWkSht(pWkshtPathName As String, pVendor As String, pMthYr As String)
   Dim xlx As Object       'Excel object
   Dim xlw As Object       'Workbook
   Dim xls As Object       'Worksheet`
   Dim xlc As Object       'Cell reference
   Dim blnEXCEL As Boolean

   blnEXCEL = False

   ' Establish an EXCEL application object
   On Error Resume Next
   Set xlx = GetObject(, "Excel.Application")
   If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.Application")
      blnEXCEL = True
   End If
   Err.Clear
   On Error GoTo 0

   ' Change True to False if you do not want the workbook to be
   ' visible when the code is running
   xlx.Visible = False

   ' Replace C:\Filename.xls with the actual path and filename
   ' of the EXCEL file into which you will write the data
   Set xlw = xlx.Workbooks.Open(pWkshtPathName)

   ' Replace WorksheetName with the actual name of the worksheet in the EXCEL file
   ' (note that the worksheet must already be in the EXCEL file)
   Set xls = xlw.Worksheets(1)

   ' Replace A1 with the cell reference into which the first data value is to be written
   Set xlc = xls.Range("A1")   ' this is the first cell into which data will go


'This is my code
           '**** You would have your Conditional code here replacing mine ****
   xls.Rows("1").EntireRow.Insert
   xls.Rows("1").EntireRow.Insert
   xls.Range("A1").Select
   xls.Range("A1").FormulaR1C1 = pVendor
   xls.Range("A2").Select
   xls.Range("A2").FormulaR1C1 = pMthYr
   '---end of my code


  
  
   ' Close the EXCEL file while saving the file, and clean up the EXCEL objects
   Set xlc = Nothing
   Set xls = Nothing
   xlw.Close True   ' close the EXCEL file and save the new data
   DoEvents
   Set xlw = Nothing
   If blnEXCEL = True Then
      xlx.Quit
   End If
   Set xlx = Nothing
End Sub

And, of course, EVERY module should have these two lines as the top two lines

Option Compare Database
Option Explicit

The code for "EditVendorWkSht" has been adapted from Ken Snell's site http://www.accessmvp.com/KDSnell/EXCEL_Export.htm