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
Bookmarks