Conditional Formatting

[riaarora]

Hi

I want to change the Font Colour on based of some condition
e.g.
If number is 100% and above then change the font color to Green
If it's above 95% and Less than 100% then change font color Amber
If it's less than 95% then change color to Red

I'm using below code:

Sub setRAGStatus(strRange As String)
    objXLSheet.range(strRange).Select
    objXLSheet.range(strRange).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=1"
    objXLSheet.range(strRange).FormatConditions(Selection.FormatConditions.count).SetFirstPriority
    objXLSheet.range(strRange).FormatConditions(1).Font.Color = RGB(0, 255, 0)
    
    objXLSheet.range(strRange).Select
    objXLSheet.range(strRange).FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=0.95", Formula2:="=1"
    objXLSheet.range(strRange).FormatConditions(Selection.FormatConditions.count).SetFirstPriority
    objXLSheet.range(strRange).FormatConditions(1).Font.Color = RGB(255, 153, 0)
    
    objXLSheet.range(strRange).Select
    objXLSheet.range(strRange).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0.95"
    objXLSheet.range(strRange).FormatConditions(Selection.FormatConditions.count).SetFirstPriority
    objXLSheet.range(strRange).FormatConditions(1).Font.Color = RGB(255, 0, 0)

End Sub

This code worked only once but now it's not working quite wiered

I'm getting error below error
"Run time error 91"
"Object variable or with block variable not set"

Above error I'm getting at below line

objXLSheet.range(strRange).FormatConditions(Selection.FormatConditions.count).SetFirstPriority

Highly appreciate your help

[Rod]

Hi,

First, this is a Microsoft Access forum and you are presenting an Excel problem; you may have better luck elsewhere; but then maybe you are using Office Automation from within Access.

Having said that, let me see if I can help. First, the error: you have no 'With - End With' block so VBA is bitching that an object is not set. From the fact that the error occurs on the 'SetFirstPriority' line then the only candidate is the 'Selection' object; it can't be 'objXLSheet' because that was OK in the previous line, also the syntax of defining the range object is identical. You may verify that 'Selection' is the culprit in the Immediate Window. Thus for some reason 'objXLSheet.range(strRange).Select' is not creating a selection object (or it's being destroyed in the next line). Again use the debugging tools to find out what is happening.

I have used FormatConditions quite a lot in Access. All my code assumes the collection is numbered from zero - and my code works. Unless Excel is different (surely not) or there is a switch I'm unaware of to make collections base 1, then your code is suspect.

As for the code itself, there's a great deal of redundancy. Here's what I would try, no guarantees as I have not and cannot test it.

Sub setRAGStatus(strRange As String)
    dim objRange as Range
    set objRange = objXLSheet.Range(strRange)
    With objRange
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=1"
        .FormatConditions(0).Font.Color = RGB(0, 255, 0)
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=0.95", Formula2:="=1"
        .FormatConditions(1).Font.Color = RGB(255, 153, 0)
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0.95"
        .FormatConditions(2).Font.Color = RGB(255, 0, 0)
        .FormatConditions(0).SetFirstPriority
    End With
End Sub

I have sequentially added three format conditions numbered zero to two. I have then given condition zero priority since the boundary of 100% exists in both conditions zero and one. I've included the delete for safety; I don't know how Excel stores the conditions and maybe it's wise to ensure any persistent conditions are first deleted; I can then be confident of the numbering.

Alright, I have avoided rather than solved the problem with 'Selection.' I know Excel can be finiky in this respect and I usually find it's because I have more than one copy of Excel running at a time (happens a lot with automation when run-time errors occur and restarts are made). The cure is to exit Excel completely - sometimes it needs a reboot - and then try again.

Please read following post!

[Rod]

Important: from further research it appears that Excel does differ from Access in that the FormatConditions collection is base 1. Please change 0 to1, 1 to 2 and 2 to 3 in my sample code. Also from my research I came across a statement that a range may have no more than three conditions - but I think your code is setting conditions at the cell level.