Option Explicit



Function GetRecordNumber(CaseNumber) As Long
'Returns 0 if CaseNumber Not Found
'Returns Row Number of CaseNumber If Found
'Assumes that each Case Number only occurs once on the sheet

Dim NextRow As Long 'Speedy search
Dim X As Range

  NextRow = Range("A" & Rows.Count).End(xlUp).Row + 1
  Set X = Range("C2:C" & NextRow).Find(CaseNumber)
  
  If X Is Nothing Then
    GetRecordNumber = 0
  Else
    GetRecordNumber = X.Row
  End If

End Function

Function GetNextRecordNumber() As Long
  GetNextRecordNumber = Range("A" & Rows.Count).End(xlUp).Row + 1
End Function

Function GetLastFieldNum() As Long
'Assumes Intake Table has empty column to right side
  GetLastFieldNum = Range("A1").End(xlToRight).Column
End Function


Function SetColors(RecordNumber)
'Assumes there is an empty column at the right of the Intake table

If RecordNumber = "" Then Exit Function

  Dim LastCol As Long
  Dim ICI As Long 'Interior ColorIndex #
  Dim FCI As Long 'Font ColorIndex #
  
  Const DefaultICI As Long = 16
  Const DefaultFCI As Long = -4105
  
  LastCol = Range("A1").End(xlToRight).Column
  
    Select Case UCase(Range("A" & RecordNumber).Value)
      Case "OPEN":        ICI = 16  'Dark Grey
      Case "SERVE":       ICI = 10  'Green
      Case "BAD ADDRESS": ICI = 46  'Orange
      Case "RE-DATE":     ICI = 44  'Dark Yellow
      Case "STOP SERVE":  ICI = 30  'Dark red
                          FCI = 2   'White
      Case "RTO":         ICI = 13  'Purple
                          FCI = 2    'White
      Case Else
                          ICI = DefaultICI
                          FCI = DefaultFCI
    End Select
    
  With Range("A" & RecordNumber).Resize(, LastCol)
    .Interior.ColorIndex = ICI
    .Font.ColorIndex = FCI
  End With
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
If DesignMode Then Exit Sub

'This should be done by Form input box validation thus allowing proper names to be
'in Familiar format (Proper Name) and making select values stand out by all UPPERCASE

    Dim Cel As Range 'Cell is too similar to a VBA Key word (Cells)
    On Error Resume Next
    Application.EnableEvents = False
    For Each Cel In Target
        Cel = UCase(Cel)
    Next
    Application.EnableEvents = True
End Sub