Macro that is column specific & changes cell color depending on cell contents

Hi All,

So i'm trying to create a macro that is specific to only a specific part of column F. More information can be added to column, so i would need the range to be dynamic.

The data in column F uses VLOOKUP to populate the cells, however information can also be manually inputted to override the VLOOKUP data. Finally, at the bottom of column F there are cells containing the SUM Function, and these cells add data from various other parts of the spreadsheet.

I am trying to provide a different color for the manually inputted cells, the VLOOKUP cells and leave any other cells in this column (containing text, the totals or empty) with a "No Fill Font".

I would also like to initiate this Macro whenever a change is made to the cell. So if for example i was to manually enter a value in Column F, the macro will initiate automatically, without me having to press "Run Macro"

Below is my code to-date.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F4:F165")) Is Nothing Then KeyCellsChanged
End Sub

Sub KeyCellsChanged()
  Dim Cell As Object
  
  For Each Cell In Range("F4:F165")
  
  If Cell.HasFormula = True And 1 < Cell < 300 Then
        Cell.Interior.ColorIndex = 3

  ElseIf 1 < Cell < 300 Then
  
        Cell.Interior.ColorIndex = 40
    

  End If
  
  Next Cell

End Sub