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 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.

[vbfbryce]

You don't need code for this; you can use "conditional formatting."

What version of Excel do you have?

[riaarora]

This needs to be done using MSAccess VBA

[vbfbryce]

You want to change the fonts in an Excel sheet via Access VBA, is that right?

[riaarora]

Yes. I'm creating Excel using Access VBA. After creating the excel I have done some formatting and need to change the colour of selected few ranges e.g. from "C3:C10" if number is 100% and above then change number colour to Green
If it's above 95% and Less than 100% then Amber
If it's less than 95% then Red

[vbfbryce]

Here is some code from Access (2002) that works on my Excel 2010. I only put the "less than 95%" condition in there. When you have more than 1 condition, you need to set a priority order for them.

Private Sub O_Sheet()
    Dim xlsApp As Excel.Application
    Dim xlsBook As Excel.Workbook
    Dim i As Integer

    Set xlsBook = Workbooks.Open("C:\Users\myPath\VB\AnyFile.xlsx")
    
    For i = 1 To 2
        Range("a" & i).FormatConditions.Add xlCellValue, Operator:=xlLess, Formula1:="0.95"
        Range("a" & i).FormatConditions(1).Font.Color = -16383844   'red for LT 95%
    Next i
    
    xlsBook.Close
    
    Set xlsBook = Nothing
    
End Sub

[riaarora]

Thanks for the reply.

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

Value of strRange is S4:T18

It's not changing the Font Color

[riaarora]

I changed the code and that worked only once:

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.

[vbfbryce]

Not sure why it's not working for you. Here is my version:

Private Sub O_Sheet()
    Dim xlsBook As Excel.Workbook
    Dim i As Integer

    Set xlsBook = Workbooks.Open("C:\YourPath\YourFile.xlsx")
    
    For i = 1 To 2
        Range("a" & i).FormatConditions.Add xlCellValue, Operator:=xlEqual, Formula1:="1"
        Range("a" & i).FormatConditions(1).Font.Color = -16383844   'green for = 1
        Range("a" & i).FormatConditions(1).Font.Bold = False
        Range("a" & i).FormatConditions(1).Font.Italic = True
    Next i
    
    For i = 1 To 2
        Range("a" & i).FormatConditions.Add xlCellValue, Operator:=xlBetween, Formula1:="=0.95", Formula2:="=1"
        Range("a" & i).FormatConditions(1).Font.Color = -16383844   'red for LT 95%
        Range("a" & i).FormatConditions(1).Font.Bold = True
    Next i
    
    xlsBook.Save
    xlsBook.Close
    
    Set xlsBook = Nothing
    
    MsgBox "done"

End Sub