none
Help with Excel VBA - Conditional Formatting Based On Formula RRS feed

  • Question

  • Dear Expert,

    Please help this Excel VBA noob with conditional formatting.

    Essentially, I am trying to apply conditional formatting on each cell within a range, and if the cell value divided by a constant cell is over 0.9 then make it green, and if the cell value divided by the same constant cell is less than 0.5 thn make it orange. The code probably explains better than me.

    For Each cell In assignment1Score
     
    With cell
       .FormatConditions.Add Type:=xlExpression, _
        Formula1:="=cell.Value / assignment1Cell.Value > 0.9"
       .FormatConditions(1).Interior.Color = RGB(96, 169, 23)
       .FormatConditions.Add Type:=xlExpression, _   
        Formula1:="=cell.Value / assignment1Cell.Value < 0.5"
       .FormatConditions(2).Interior.Color = RGB(250, 104, 0)
    End With
             
    Next cell

    However, the code does not work. Any suggestions?

    Many thanks - really appreciate it!

    Monday, November 25, 2013 8:47 AM

Answers

  • You can add a rule to check if the cell is empty. This should be the first rule, so:

    Sub SetCF()
        With Range("A2:A100")
            .FormatConditions.Add(Type:=xlCellValue, _
                Operator:=xlEqual, Formula1:="=""""") _
                .Interior.ColorIndex = xlColorIndexNone
            .FormatConditions.Add(Type:=xlCellValue, _
                Operator:=xlGreater, Formula1:="=0.9*$L$7") _
                .Interior.Color = RGB(96, 169, 23)
            .FormatConditions.Add(Type:=xlCellValue, _
                Operator:=xlLess, Formula1:="=0.5*$L$7") _
                .Interior.Color = RGB(250, 104, 0)
        End With
    End Sub

    You cannot use conditional formatting to set the value of a cell, only to set its formatting.

    The easiest way to set a value conditionally is to use a cell formula. Let's say you have a named range gradeBoundaries that looks like this:

    0    F
    60  C
    70  B
    80  A
    90  A+

    (this is just an example, of course)

    With a score in cell P2, you can enter the following formula in Q2:

    =VLOOKUP(P2,gradeBoundaries,2)

    This formula can be filled down.

    Using VBA:

    a1Grade.FormulaR1C1 = "=VLOOKUP(RC[-1],gradeBoundaries,FALSE)"

    where a1Grade is a Range object.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by ansonmiu14 Tuesday, November 26, 2013 11:24 AM
    Tuesday, November 26, 2013 10:49 AM
  • You could use

    a1Grade.FormulaR1C1 = _
       
    "=IFERROR(VLOOKUP(RC[-1],gradeBoundaries,FALSE),"""")"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by ansonmiu14 Tuesday, November 26, 2013 11:57 AM
    Tuesday, November 26, 2013 11:30 AM
  • You could use something like

    somecell.FormulaR1C1 = "=R[" & rowoffset & "]C[" & columnoffset & "]/" & assignmentMark.Value & "*100"

    where rowoffset and columnoffset are numeric variables specifying the offset (positive is down/to the right, negative is up/to the left).

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by ansonmiu14 Tuesday, November 26, 2013 2:37 PM
    Tuesday, November 26, 2013 12:01 PM

All replies

  • cell is a VBA variable. You cannot use cell or cell.Value within a formula string.

    Let's say assignment1Score is the range A2:A100 and assignment1Cellis the cell L7:

    Sub SetCF()
        With Range("A2:A100")
            .FormatConditions.Add(Type:=xlCellValue, _
                Operator:=xlGreater, Formula1:="=0.9*$L$7") _
                .Interior.Color = RGB(96, 169, 23)
            .FormatConditions.Add(Type:=xlCellValue, _
                Operator:=xlLess, Formula1:="=0.5*$L$7") _
                .Interior.Color = RGB(250, 104, 0)
        End With
    End Sub

    Note that we don't have to loop through the cells.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by ansonmiu14 Tuesday, November 26, 2013 10:19 AM
    • Unmarked as answer by ansonmiu14 Tuesday, November 26, 2013 10:51 AM
    Monday, November 25, 2013 10:56 AM
  • Thank you so much - how can I add another rule stating to not apply any formatting if the cell is empty, because right now all cells are orange unless add a number.

    Now I face another problem, and I would appreciate some professional advice.

    Essentially this is a mark book that I am creating. The user fills in information about the lesson on a UserForm, and the code here is used to transfer the information from the UserForm to the sheet. I am right now trying to apply conditional formatting to a column of grades, so each cell in the grades column would show a grade (i.e. A*, A, B) depending on the value of the cell to the left of it. The code I feebly came upw ith obviously doesnt work, and you can see why.

                For Each cell In a1Grade
                    If cell.Offset(0, -1).Value / Assignment1Mark.Value * 100 > gradeBoundaries.Range("B1").Value Then
                        cell.Value = gradeBoundaries.Range("A1").Value
                    ElseIf cell.Offset(0, -1).Value / Assignment1Mark.Value * 100 > gradeBoundaries.Range("B2").Value Then
                        cell.Value = gradeBoundaries.Range("A2").Value
                    ElseIf cell.Offset(0, -1).Value / Assignment1Mark.Value * 100 > gradeBoundaries.Range("B3").Value Then
                        cell.Value = gradeBoundaries.Range("A3").Value
                    ElseIf cell.Offset(0, -1).Value / Assignment1Mark.Value * 100 > gradeBoundaries.Range("B4").Value Then
                        cell.Value = gradeBoundaries.Range("A4").Value
                    ElseIf cell.Offset(0, -1).Value / Assignment1Mark.Value * 100 > gradeBoundaries.Range("B5").Value Then
                        cell.Value = gradeBoundaries.Range("A5").Value
                    ElseIf cell.Offset(0, -1).Value / Assignment1Mark.Value * 100 > gradeBoundaries.Range("B6").Value Then
                        cell.Value = gradeBoundaries.Range("A6").Value
                    ElseIf cell.Offset(0, -1).Value / Assignment1Mark.Value * 100 > gradeBoundaries.Range("B7").Value Then
                        cell.Value = gradeBoundaries.Range("A7").Value
                    Else
                    
                    End If
                Next cell

    I know that I cannot refer to cells like thi,s but I don't know how to use conditional formatting for this. Putting if statements wont work as the user will eddit the speadsheet data later so it has to be conditional formatting. Could you help me with this?

    Thank you so much~

    Tuesday, November 26, 2013 10:26 AM
  • You can add a rule to check if the cell is empty. This should be the first rule, so:

    Sub SetCF()
        With Range("A2:A100")
            .FormatConditions.Add(Type:=xlCellValue, _
                Operator:=xlEqual, Formula1:="=""""") _
                .Interior.ColorIndex = xlColorIndexNone
            .FormatConditions.Add(Type:=xlCellValue, _
                Operator:=xlGreater, Formula1:="=0.9*$L$7") _
                .Interior.Color = RGB(96, 169, 23)
            .FormatConditions.Add(Type:=xlCellValue, _
                Operator:=xlLess, Formula1:="=0.5*$L$7") _
                .Interior.Color = RGB(250, 104, 0)
        End With
    End Sub

    You cannot use conditional formatting to set the value of a cell, only to set its formatting.

    The easiest way to set a value conditionally is to use a cell formula. Let's say you have a named range gradeBoundaries that looks like this:

    0    F
    60  C
    70  B
    80  A
    90  A+

    (this is just an example, of course)

    With a score in cell P2, you can enter the following formula in Q2:

    =VLOOKUP(P2,gradeBoundaries,2)

    This formula can be filled down.

    Using VBA:

    a1Grade.FormulaR1C1 = "=VLOOKUP(RC[-1],gradeBoundaries,FALSE)"

    where a1Grade is a Range object.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by ansonmiu14 Tuesday, November 26, 2013 11:24 AM
    Tuesday, November 26, 2013 10:49 AM
  • Thank you so much - it worked!!!

    Would it be possible to remove the #Value! when no score is entered by using an IF statement? Im not sure whether it works with R1C1.

    Equally, how can I use R1C1 to fill a formula for a percentage? Similar to the grades, I want one column of percentages to equate itself using the score from the cell of its immediate left and the value from the text box on the UserForm, something like =RC[-1] / assignment1Mark.Value * 100. Would that work properly on R1C1?

    Thank you!!

    Tuesday, November 26, 2013 11:26 AM
  • You could use

    a1Grade.FormulaR1C1 = _
       
    "=IFERROR(VLOOKUP(RC[-1],gradeBoundaries,FALSE),"""")"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by ansonmiu14 Tuesday, November 26, 2013 11:57 AM
    Tuesday, November 26, 2013 11:30 AM
  • Would it be possible to use integer variables in R1C1 style, because my macro also identifies the row and column of an offset, so would R(assignmentR)C(assignmentC) be possible?
    Tuesday, November 26, 2013 11:57 AM
  • You could use something like

    somecell.FormulaR1C1 = "=R[" & rowoffset & "]C[" & columnoffset & "]/" & assignmentMark.Value & "*100"

    where rowoffset and columnoffset are numeric variables specifying the offset (positive is down/to the right, negative is up/to the left).

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by ansonmiu14 Tuesday, November 26, 2013 2:37 PM
    Tuesday, November 26, 2013 12:01 PM