Conditional Formatting

R

ria.arora

Registered User.
Local time
Today, 18:45
Joined
Jan 25, 2012
Messages
109
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
 
Excel report is generated from ACCESS VBA. Conditonal formatting (TrafficLights) need to be applied from ACCESS VBA in Excel.

There is no compilation </SPAN>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 </SPAN>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
 
Here is your code with the fixes:
Code: 
Sub setRAGStatusFontColorIcon(strRange As String)
    Dim objISet As Excel.IconSetCondition
    objXLApp.ActiveSheet.Range(strRange).Select
    With objXLApp.Selection
        .FormatConditions.Delete
    End With
    With objXLApp.ActiveSheet.Range(strRange)
        .Select
        Set objISet = .FormatConditions.AddIconSetCondition
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With objISet
            .ReverseOrder = True
            .ShowIconOnly = False
            .IconSet = objXLApp.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
 
Dear Boblarson

Excellent! Thanks for the help. It's working but only issue it's is not showing the correct result. I want:

If it's ABOVE 100% or 1 then GREEN
If it's BETWEEN 90% to 100% or 0.9 and 1 then AMBER
If it's LESS than 90% or 0.9 then RED.

Currently it's now showing AMBER. I tried to add another "With .IconCriteria(1)" but that is not working. Any idea how to add that as well.

Thanks a lot for the help.


Code: 
Sub setRAGStatusFontColorIcon(strRange As String)
    Dim objISet As Excel.IconSetCondition
    objXLApp.ActiveSheet.range(strRange).Select
    With objXLApp.Selection
        .FormatConditions.Delete
    End With
    With objXLApp.ActiveSheet.range(strRange)
        .Select
        Set objISet = .FormatConditions.AddIconSetCondition
        .FormatConditions(.FormatConditions.count).SetFirstPriority
        With objISet
            .ReverseOrder = False
            .ShowIconOnly = False
            .IconSet = objXLApp.ActiveWorkbook.IconSets(xl3TrafficLights1)
            With .IconCriteria(3)
                .Type = xlConditionValueFormula
                .Value = "=.9"
                .Operator = 5
            End With
            With .IconCriteria(2)
                .Type = xlConditionValueFormula
                .Value = "=1"
                .Operator = 5
            End With
        End With
    End With
End Sub
 
Try adding one with (4) instead. The first one is the default (what it is to be when nothing matches) and so the 3 you set would be 2, 3, 4 (I believe).
 
I got the error "Subscript out of range"

With .IconCriteria(4) '-----> ERROR "Subscript out of range"


Code: 
Sub setRAGStatusFontColorIcon(strRange As String)
    Dim objISet As Excel.IconSetCondition
    objXLApp.ActiveSheet.range(strRange).Select
    With objXLApp.Selection
        .FormatConditions.Delete
    End With
    With objXLApp.ActiveSheet.range(strRange)
        .Select
        Set objISet = .FormatConditions.AddIconSetCondition
        .FormatConditions(.FormatConditions.count).SetFirstPriority
        With objISet
            .ReverseOrder = False
            .ShowIconOnly = False
            .IconSet = objXLApp.ActiveWorkbook.IconSets(xl3TrafficLights1)
            With .IconCriteria(4)  '-----> ERROR "Subscript out of range"
                .Type = xlConditionValueFormula
                .Value = "=1"
                .Operator = 5
            End With
            With .IconCriteria(3)
                .Type = xlConditionValueFormula
                .Value = "=.9"
                .Operator = 5
            End With
            With .IconCriteria(2)
                .Type = xlConditionValueFormula
                .Value = "=.8"
                .Operator = 5
            End With
        End With
    End With
End Sub
 
Sorry, took me a bit to figure it out. By the way, I normally figure these things out by letting Excel record a macro while I do it and then just modify the code slightly to fit within Access.
Code: 
Sub setRAGStatusFontColorIcon(strRange As String)
    Dim objISet As Excel.IconSetCondition
    objXLApp.ActiveSheet.Range(strRange).Select
    With objXLApp.Selection
        .FormatConditions.Delete
    End With
    With objXLApp.ActiveSheet.Range(strRange)
        .Select
        Set objISet = .FormatConditions.AddIconSetCondition
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With objISet
            .ReverseOrder = False
            .ShowIconOnly = False
            .IconSet = objXLApp.ActiveWorkbook.IconSets(xl3TrafficLights1)
            With .IconCriteria(2)
                .Type = xlConditionValueNumber
                .Value = 0.9
                .Operator = 7
            End With
            With .IconCriteria(3)
                .Type = xlConditionValueNumber
                .Value = 1
                .Operator = 7
            End With
        End With
    End With
End Sub
 
Dear Boblarson,

Perfect! Thanks a lot for the help.
 
Dear Bob,

I want to add one more condition in existing code. If value is less 0.1%(means it's negative) then also want to show GREEN instead of RED.

Code: 
Sub setRAGStatusFontColorIconException(strRange As String)
    Dim objISet As Excel.IconSetCondition
   
    objXLApp.ActiveSheet.range(strRange).Select
    With objXLApp.ActiveSheet.range(strRange)
        .Select
        .FormatConditions.AddIconSetCondition.Delete
        Set objISet = .FormatConditions.AddIconSetCondition
        .FormatConditions(.FormatConditions.count).SetFirstPriority
        With objISet
            .ReverseOrder = False
            .ShowIconOnly = False
            .IconSet = objXLApp.ActiveWorkbook.IconSets(xl3TrafficLights1)
            With .IconCriteria(2)
                .Type = xlConditionValueNumber
                .Value = 0.9
                .Operator = 7
            End With
            With .IconCriteria(3)
                .Type = xlConditionValueNumber
                .Value = 1
                .Operator = 7
            End With
            With .IconCriteria(3)
                .Type = xlConditionValueNumber
                .Value = -0.1
                .Operator = 7
            End With
        End With
    End With
End Sub

I have added below extra code but this does not work.

Code: 
With .IconCriteria(3)
                .Type = xlConditionValueNumber
                .Value = -0.1
                .Operator = 7
            End With

Please help

Regards
Ria
 
I don't think you can use more than three values since it is three lights. And you can try
.Value = -0.1 OR >1

But I don't think it will work as this one is basically asking for split points. So I think you are out of luck for this particular one.
 
Dear Bob,

I tried .Value = -0.1 OR >1 also tried .Value = -0.1 OR .Value > 1

but it's giving error.
 
ria.arora said:
but it's giving error.
Click to expand...
As I thought it would. Like I said, with that format, it is using a SPLIT point to determine which is on top, which is in the middle, and which is in the lower part. You have to pass it ONE specific non-overlapping value for each part.
 
You must log in or register to reply here.

Similar threads

D
Controlling Format Condition in VBA
Replies
3
Views
1,902
theDBguy
theDBguy
Rx_
Excel - CopyFromRecordset - Set Rules for values
Replies
2
Views
3,217
Rx_
Rx_
I
Create XLS with conditional formatting
Replies
4
Views
2,587
pbaldy
pbaldy
B
Access 2007 to format conditionals in Excel
Replies
4
Views
2,528
boblarson
boblarson
J
Conditional Formatting in Access report bu using VBA code
Replies
9
Views
24,950
jp2468
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom