Conditional Formatting

[ria.arora]

Hi

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

And this need to be changed for a range e.g. D120

I'm not sure how to apply that condition.

Appreciate your help with sample code.

 

[mdlueck]

Do you have a Multiple Items form and you wish to color code the text of the row based on the values of the same row?

Or is this a single record form?

 

[ria.arora]

This needs to be done on given range (multiple lines) on based of value in the cell

 

[mdlueck]

This needs to be done on given range (multiple lines) on based of value in the cell

Multiple records forms in Access have a single "record row template" in the form UI in design mode, which is replicated as many times as is needed to list each of the multiple records to be listed.

I implemented selected record suggestion - found a bug or two implementing it.

"Highlight Current Record in an Access Continuous Form"
http://www.upsizing.co.uk/Art53_Highlight.aspx

Further, I have utilized the other two conditional formatting slots A2007 makes available to support further coloring. An example of the results may be found here:
http://www.access-programmers.co.uk/forums/showthread.php?t=217831&page=3#post1110945

So certainly what you could do is model after the suggestion to place a box behind the field controls, set the field controls to transparent background, and install the conditional formatting rules based on your requirements.

 

[ria.arora]

Thanks Michael.

I need to change the font color only (not background of the row).

If cells value is 100% and above then change value's colour to Green (default is Black)
If it's above 95% and Less than 100% then check font color to Amber
If it's less than 95% then change font color to Red

 

[ria.arora]

I have used below the code but this is not working

Sub setRAGStatus(strRange As String)
    objXLSheet.range(strRange).Select
    objXLSheet.range(strRange).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=1"
    objXLSheet.range(strRange).FormatConditions(1).Font.Color = -11489280
    objXLSheet.range(strRange).FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=0.95", Formula2:="=1"
    objXLSheet.range(strRange).FormatConditions(1).Font.Color = RGB(247, 153, 75)
    objXLSheet.range(strRange).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0.95"
    objXLSheet.range(strRange).FormatConditions(1).Font.Color = -16776961
End Sub

End SubValue of strRange is "S4:T18"

It's not changing the Font Color

 

[ria.arora]

I changed the code to below:

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 :banghead:

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

Please help

 

[VilaRestal]

Part of the problem is that this is an Excel question in an Access forum without mentioning that it was. mdlueck may be a bit annoyed about you not mentioning that.

As for your code, firstly: Why is the range passed as a string to this sub? Surely better to pass the range as a reference.

Secondly: the lines
FormatConditions(1).Font.Color
should they not be
FormatConditions(Selection.FormatConditions.count).Font.Color
as per the line before.

But then thirdly, why are you selecting the range and referring to the Selection when you already have the range?

I think what it should be is:

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

so all in all:

Sub setRAGStatus(ByRef rng As Range)
    With rng
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=1"
        With .FormatConditions(.FormatConditions.count)
              .SetFirstPriority
              .Font.Color = RGB(0, 255, 0)
        End With
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=0.95", Formula2:="=1"
        With .FormatConditions(.FormatConditions.count)
              .SetFirstPriority
              .Font.Color = RGB(255, 153, 0)
        End With
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0.95"
        With .FormatConditions(.FormatConditions.count)
              .SetFirstPriority
              .Font.Color = RGB(255, 0, 0)
        End With
    End With
End Sub

 

[mdlueck]

Part of the problem is that this is an Excel question in an Access forum without mentioning that it was. mdlueck may be a bit annoyed about you not mentioning that.

Gggaaaggg, you mean the OP was asking about a Spreadsheet-Database?!!?!? Yuck!!!!

 

[vbaInet]

I've mentioned to the OP on several occasions that this is not the right section for VBA Excel questions but he or she is not taking heed.