Results 1 to 3 of 3

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

Threaded View

  1. #1
    Registered User
    Join Date
    08-25-2012
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    1

    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
    Last edited by Cutter; 08-25-2012 at 01:48 PM. Reason: Added code tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1