+ Reply to Thread
Results 1 to 2 of 2

Conditional Formatting via VBA: Change formatting in range based on value of each cell

  1. #1
    Registered User
    Join Date
    01-18-2013
    Location
    Michigan, USA
    MS-Off Ver
    MS Office 2016
    Posts
    12

    Conditional Formatting via VBA: Change formatting in range based on value of each cell

    Present code below, placed in worksheet where changes are desired.
    When values (format - general) in cells within 'grid' range change, there are no errors, but no formatting change. Nothing appears to happen.
    I admit I'm fuzzy on finer points of 'Intersect/Target' - this just checks to ensure changed cell to be formatted falls within specified range, correct?
    Also, this is step 1. Once I get this working, I need to implement multi-leveled conditional formatting.
    Ex:
    Cell C3: Contains a validation/drop-down list
    Range D4:U23: The range of cells whose formats need to change based on their individual cell value
    Goal: When value in cell C3 changes, one set of conditional formats applies to the data that appears in Range D4:U23 (via Index/Match which finds data in another worksheet).

    So;
    -User selects 'Year' - formulas find year data on other sheet and place in cells - VBA formats the numerical data in cells according to one set of conditions (ex: 1950 to 1969 is Red, 12pt, Bold... 1970 to 1979 is Blue, etc).
    -Then, user may select 'Type' from drop-down list - same formulas find text-based 'type' data and place into cells - VBA formats the text data in cells according to another, different set of conditions (ex: 'Large' is Yellow, 12pt, Italic, etc).

    Code below only addresses intermediate need to format cells within range based on values in those cells.

    Guidance much appreciated.

    Ralph.

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim grid As Range
    Dim cc As Long
    Dim fc As Long
    Dim fs As Long
    Dim fb As Boolean

    Set grid = ActiveSheet.Range("D3:U23")

    If Intersect(Target, Range("C3:U23")) Is Nothing Then End Sub

    For Each cell In grid
    Select Case cell
    Case vbNullString
    cc = 2: fc = 2
    Case 1950 To 1969
    cc = 3: fc = 2: fs = 12: fb = True
    Case 1970 To 1979
    cc = 5: fc = 1: fs = 12: fb = True
    Case 1980 To 1989
    cc = 6: fc = 1: fs = 12: fb = True
    Case 1990 To 1999
    cc = 2: fc = 1: fs = 12: fb = True
    Case 2000 To 2009
    cc = 16: fc = 2: fs = 12: fb = True
    Case 2000 To 2009
    cc = 1: fc = 2: fs = 12: fb = True
    End Select
    With cells.grid
    .Interior.ColorIndex = cc
    .Font.ColorIndex = fc
    .Font.Size = fs
    .Font.Bold = fb
    End With
    Next
    End Sub
    Attached Images Attached Images
    Last edited by ralphjmedia; 04-11-2013 at 11:53 AM. Reason: Edit 11:53am 4/11/13 - found code error, but no bearing on stated issue.

  2. #2
    Registered User
    Join Date
    01-18-2013
    Location
    Michigan, USA
    MS-Off Ver
    MS Office 2016
    Posts
    12

    Re: Conditional Formatting via VBA: Change formatting in range based on value of each cell

    Nothing? Harder than what I assumed was fundamental oversight on my part? Anyone?

    5 days, no thoughts on this? Question poorly worded? Impossible? help?
    Last edited by ralphjmedia; 04-15-2013 at 06:59 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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