title: Changing cell colors in vba if sum on each row is not correct
body: <p>I am a video person, and only a beginner excel wiz, so this current challenge has me in an area where i have less experience than most in the forum. I need to build a macro which i can install on less savvy user's excel toolbars to do some data checking for me.</p>
  
  <p>I have excel documents with 3 relevant columns and up to 999 rows. Each row in the worksheet represents a video shot.</p>
  
  <p>Col D is a "frame in" value (an integer from 1 to 999,999)
  Col E is a "frame out" value (also an integer from 1 to 999,999)
  Col F is a "duration" value which SHOULD be (Col E - Col D + 1)</p>
  
  <p>I have a VBA script (that i Frankenstein-ed together from code on this site) which checks for things in other columns (A, B, C) related to shotname length, etc. </p>
  
  <p>Now I want to call a new function which will color the duration column (col F) cell background  if there is an error in the worksheet and the duration of the shot is longer or shorter than the in/out values would suggest. (and also perhaps if the "frame out" of one shot is not equal to the "frame in" of the next row (minus one).</p>
  
  <p>I have searched the site exhaustively, but can't seem to locate a function which loops through my range of cells and does math to the ROWS individually, instead of totalling the COLS.</p>
  
  <p>Any pointers would be most helpful. I've resisted asking a question because i'm stubborn and want to do it myself, but i'm running out of time. I have obviously figured out how to do it with formulas, but I can't easily train others on my team to do the formulas. </p>
  
  <p>Jamie</p>
  
  <p>In response to the request below, there is a function i am using to see if shot names in Col A are unique... sorry if the formatting is a little off!</p>
  
  <pre><code>Private Sub CntUniqueShotNamesRowA()
  
  'This routine enumerates the number of unique values in the Shot Names Column (a).
  'it returns the number of unique items in the range of (a:a)
  
  Dim Uni As Collection, cl As Range, LpRange As Range
  
  Dim clswfrm As Range, clswcst As Range, myRng As Range
  Dim TotUni As Long
  Set myRng = Sheets(ActiveSheet.Name).[a:a] 'sheet/range defined here
  
  On Error Resume Next
  Set clswfrm = myRng.SpecialCells(xlFormulas)
  Set clswcst = myRng.SpecialCells(xlConstants)
  Set myRng = Nothing 'free up memory
  On Error GoTo 0
  
  If clswfrm Is Nothing And clswcst Is Nothing Then
      MsgBox "No Unique Cells? (or no content?)"
      Exit Sub
  ElseIf Not clswfrm Is Nothing And Not clswcst Is Nothing Then
      Set LpRange = Union(clswcst, clswfrm)
  ElseIf clswfrm Is Nothing Then Set LpRange = clswcst
  Else: Set LpRange = clswfrm
  End If
  
  Set clswfrm = Nothing: Set clswcst = Nothing 'Free up memory
  Set Uni = New Collection
  
  On Error Resume Next
  For Each cl In LpRange
      Uni.Add cl.Value, CStr(cl.Value) 'assign unique key string
  Next cl
  On Error GoTo 0
  
  Set LpRange = Nothing 'free up memory
  TotUni = Uni.Count
  Set Uni = Nothing ''free up memory
  MsgBox "There are " &amp; TotUni &amp; " unique shot names in Row A."
  End Sub
  </code></pre>
  
  <p>Here is my pseudocoded attempt to make this work.... apologies to any who are confused by my   comments.</p>
  
  <pre><code>Sub FlagBadShotDurations()
  
  ' This routine will enumerate the length of each shot from the 
  ' in and out frames in Col D and Col E.
  ' it will then compare with the listed duration in col F.
  ' if F is not matching the calculated value it will 
  ' flag the cell with a yellow BG
  
  'set range and counter
  Dim myRng As Range
  Dim ShotCount As Long
  
  'set places to do formula work
  Dim FrameIn As Long
  Dim FrameOut As Long
  Dim calculatedDuration As Long
  Dim Duration As Long
  
  'count problem shots
  Dim TotalIssues As Long
  
  
  Set myRng = Sheets(ActiveSheet.Name).[f:f] 'sheet/range defined here
  Set ShotCount = 1
  
  For Each ShotCount In myRng
      'Set FrameIn as the cell value for the D column
      'Set FrameOut as the cell value for the E column
      'Set Duration as the cell value for the F column
  
      ' if FrameOut - FrameIn +1 &lt;&gt; Duration then
      'set TotalIssues = TotalIssues + 1
      'set BG color of Duration Cell to Yellow.
  
  Next ShotCount
  
  If TotalIssues &gt; 1 Then
      MsgBox "Enumerated Durations from Ins and Outs and marked" &amp; TotalIssues &amp; "problems"
  Else
      MsgBox "Enumerated Durations from Ins and Outs and found no problems"
  End If
  
  End Sub
  </code></pre>