title: Trouble applying conditional formatting via udf
body: <p>So I'm working on a udf that I will eventually make into an addin to help process data and it would be preferred if cells could be highlighted to draw the user's attention to any problems.</p>
  
  <p>The udf is as follows:</p>
  
  <pre><code>Function AnalyzeData()
  Dim Headers() As Variant
  Dim Row1 As Variant
  
  Headers = Array(Row1, Row2, Row3, Row4, Row5)
  
  Row1 = Array("Final Curve", "", "Cr", "Fe", "Ni", "Cu", "Zn", "As", "Ag", "Cd", "Sn", "Pb")
  
  Row2 = Array("Level", "Nominal", 100, 100, 100, 100, 100, 100, 100, 100, 100, 100)
  col1 = Array("Level", "Cal 1", "Cal 2", "Cal 3", "Cal 4", "Cal 5", "Cal 6", "Cal 7", "Cal 8", "Cal 9", "Cal 10", "", "", "Level", "Cal 1", "Cal 2", "Cal 3", "Cal 4", "Cal 5", "Cal 6", "Cal 7", "Cal 8", "Cal 9", "Cal 10", "", "")
  col2 = Array("", "Nominal", "0.1", "0.2", "0.5", "1.0", "2.5", "5.0", "10", "20", "30", "50", "", "", "Nominal", "0.1", "0.2", "0.5", "1.0", "2.5", "5.0", "10", "20", "30", "50", "", "")
  
  Dim Location() As Variant
  
  Select Case TypeName(Application.Caller)
      Case "Range"
          Location = Array(Application.Caller.Row, Application.Caller.Column)
          If Location(0) = 1 And Location(1) = 1 Then
              BuildArrays
              MethodCheck
              On Error Resume Next
              threecf
  
              AnalyzeData = Row1(0)
          Else
              Select Case Location(1)
  
                  Case 1 - 2
                      Case 1
                          AnalyzeData = col1(Location(0) - 2)
                      Case 2
                          AnalyzeData = col2(Location(0) - 1)
                  Case Else
  
                      Select Case Location(0)
  
                          Case 1, 14
                              AnalyzeData = SelectedAnalytes(Location(1) - 3)
                          Case 2, 15
                              AnalyzeData = Row2(Location(1) - 1)
                          Case 3 To 12
                              AnalyzeData = Round(STDArray(Location(0) - 2,Location(1) - 3), 3)
                          Case 13, 26
                              AnalyzeData = ""
                          Case 16 To 25
                              If CalValues(Location(1) - 3, Location(0) - 15) = "" Then
                                  AnalyzeData = "N/A"
                              ElseIf Round(STDArray(Location(0) - 15, Location(1) - 3) / CalValues(Location(1) - 3, Location(0) - 15) * 100, 0) &lt;= 100 + Acceptance And Round(STDArray(Location(0) - 15, Location(1) - 3) / CalValues(Location(1) - 3, Location(0) - 15) * 100, 0) &gt;= 100 - Acceptance Then
                              AnalyzeData = Round(STDArray(Location(0) - 15, Location(1) - 3) / CalValues(Location(1) - 3, Location(0) - 15) * 100, 0) &amp; "%"
                              Else
                                  AnalyzeData = "Fail acceptance"
                              End If
  
                          End Select
                  End Select
          End If
  
      Case "String"
          AnalyzeData = Application.Caller
      Case "Error"
          AnalyzeData = "Error"
      Case Else
          AnalyzeData = "unknown"
  End Select
  
  End Function
  </code></pre>
  
  <p>And the "threecfs" sub is </p>
  
  <pre><code>Public Sub threecf()
  Dim rg As Range
  Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
  Set rg = Range("C2", Range("C2").End(xlDown))
  
  'clear any existing conditional formatting
  rg.FormatConditions.Delete
  
  'define the rule for each conditional format
  Set cond1 = rg.FormatConditions.Add(xlCellValue, xlGreater, "=1")
  Set cond2 = rg.FormatConditions.Add(xlCellValue, xlLess, "=$a$1")
  Set cond3 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$a$1")
  
  'define the format applied for each conditional format
  With cond1
  .Interior.Color = vbGreen
  .Font.Color = vbWhite
  End With
  
  With cond2
  .Interior.Color = vbRed
  .Font.Color = vbWhite
  End With
  
  With cond3
  .Interior.Color = vbYellow
  .Font.Color = vbRed
  End With
  
  End Sub
  </code></pre>
  
  <p>Which I found by looking for help with this online. 
  So without the lines </p>
  
  <pre><code>            On Error Resume Next
              threecf
  </code></pre>
  
  <p>My function works and analyzes the data appropriately, but when the "threecf" sub is called by cell A1 it errors doesn't proceed without "on error resume next" and the conditional formatting is not applied at all.</p>
  
  <p>If the range in "threecf" is changed to</p>
  
  <pre><code>rg=cells(1,1)
  </code></pre>
  
  <p>then the function still requires "on error resume next", but conditional formatting is applied but not correctly. It adds the conditions but with no formatting for each condition.</p>
  
  <p>If I run "threecfs" via F5 then it works correctly.</p>
  
  <p>Is my understanding of applying conditional formatting via udf's incorrect in that this cannot be done? </p>
  
  <p>Ideally, my udf will calculate recovery percentages for values from my data and the conditional formatting would be applied to highlight values that have issues, but does this need to be done by each cell instead of the starting cell applying it?</p>