0

I need to change a large amount of conditional formatting so I am writing a VBA subroutine. The following code sets the conditional format but the results are incorrect. By that I mean I go to the sheet and I see the conditional formatting is correct. The results are incorrect. So the formula .8*($L$9/10) = 7.92 and should result in numbers >= 8 not colored red but all are colored red.

Sub SetFirst2(ByVal TheRow As Byte, target As Range)
  ' if it is less than .8*total/10
 target.FormatConditions.Add Type:=xlCellValue, _
  Operator:=xlLess, Formula1:="="".8*($L$" & TheRow & "/10)"""
  target.FormatConditions(1).Font.ColorIndex = 3 'Red
End Sub

And this is the way I call it:

Call SetFirst2(9, ShTheSheet.Range("B9:K9"))

Column L contains this formula:

=SUM(B9:K9)
Community
  • 1
  • 1
RoseAb
  • 133
  • 1
  • 5

1 Answers1

1

You have too many double quotes in your formula definition. This should work (tested successfully with Excel 2010):

Formula1:="=.8*($L$" & TheRow & "/10)"
djikay
  • 10,450
  • 8
  • 41
  • 52