Answered by:
Help with Excel VBA  Conditional Formatting Based On Formula
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!
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

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

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

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~

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

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

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


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