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
Bookmarks