Conditional Formatting

Hi

I'm trying to apply conditonal formatting with TrafficLights using below access VBA but getting error "object doesn't support this property or method". I'm new to Access VBA please let me know what I'm doing wrong.

Code:

---

Sub setRAGStatusFontColorIcon(strRange As String)
    Dim objISet As Excel.IconSetCondition

    With objXLApp
        .FormatConditions.Delete
        Set objISet = .FormatConditions.AddIconSetCondition
        .FormatConditions(.FormatConditions.count).SetFirstPriority
        With objISet
            .ReverseOrder = True
            .ShowIconOnly = False
            .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
            With .IconCriteria(2)
                .Type = xlConditionValueFormula
                .Value = 1
                .Operator = 5
            End With
            With .IconCriteria(3)
                .Type = xlConditionValueFormula
                .Value = 0.9
                .Operator = 5
            End With
        End With
    End With
End Sub

---

Quote:

---

I'm trying to apply conditonal formatting with TrafficLights using below access VBA but getting error "object doesn't support this property or method". I'm new to Access VBA please let me know what I'm doing wrong.

---

it seems there is some variable issue . did you cross check .better write the option explicit and compile again where you declare variable. just one line up

I'm trying to apply conditonal formatting with TrafficLights using below access VBA but getting error "object doesn't support this property or method". I'm new to Access VBA please let me know what I'm doing wrong.

Code:

---

Sub setRAGStatusFontColorIcon(strRange As String)
    Dim objISet As Excel.IconSetCondition

    With objXLApp
        .FormatConditions.Delete
        Set objISet = .FormatConditions.AddIconSetCondition
        .FormatConditions(.FormatConditions.count).SetFirstPriority
        With objISet
            .ReverseOrder = True
            .ShowIconOnly = False
            .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
            With .IconCriteria(2)
                .Type = xlConditionValueFormula
                .Value = 1
                .Operator = 5
            End With
            With .IconCriteria(3)
                .Type = xlConditionValueFormula
                .Value = 0.9
                .Operator = 5
            End With
        End With
    End With
End sub

---

There is no compilation error. Please find below the code

Variables outside the sub

Code:

---

Option Compare Database
Option Explicit

Public appAccess As Access.Application
'Dim objXLApp As Object 'Excel.Application
Dim objXLApp As Excel.Application
'Dim objXLWorkbook As Object 'Excel.Workbook
Dim objXLWorkbook As Excel.Workbook
'Dim objXLSheet As Object 'Excel.Worksheet
Dim objXLSheet As Excel.Worksheet

---

There is another piece of code to create the Excel file and populate the data from ACCESS to Excel, that is working fine. After populating the numbers from Access to Excel I'm formatting using ACCESS VBA.

Code:

---


Sub setRAGStatusFontColorIcon(strRange As String)
    Dim objISet As Excel.IconSetCondition

    With objXLApp
        Set objISet = .FormatConditions.AddIconSetCondition              '---> ERROR
        .FormatConditions(.FormatConditions.count).SetFirstPriority      '---> ERROR
        With objISet
            .ReverseOrder = True
            .ShowIconOnly = False
            .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
            With .IconCriteria(2)
                .Type = xlConditionValueFormula
                .Value = 1
                .Operator = 5
            End With
            With .IconCriteria(3)
                .Type = xlConditionValueFormula
                .Value = 0.9
                .Operator = 5
            End With
        End With
    End With

---

Thread moved from the 'Database Development' forum (which is for questions about tables/SQL/etc) to the 'Office Development/VBA' forum (which is where other Access/Excel based questions belong)