Hi All, I am trying to write some code that will allow me to input a value in a cell (and change the font color and cell color) if a drop down menu is toggled to say "Manual." I am not an extensive user of VBA so I am essentially trying to copy code I've found on the internet or other models I've used in the past. Needless to say, I can't seem to get it to work. Right now, when I toggle my dropdown box to Manual, nothing happens. Was wondering if anyone might have a suggestion as to why my code isn't working. Unfortunately, because I don't use VBA regularly, I am not quite sure what additional information you might need, other than posting the code I'm using, see below. If anyone can help, I would be very grateful. Kindest Regards, Chet
-------------------------------------------------------------------------------------------------
Private Sub Cap_Rate_Change(ByVal Target As Range)
If Intersect(Target, Range("AREA_CAP_RATE_SCHED_SELECT")) Is Nothing Then GoTo EndJump:
If Application.IsError(Application.Match(Target, Worksheets("Lists").Range("LIST_CAP_RATE_SOURCE"), 0)) Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Value = "Manual" Then
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = 0.04
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = 0.04
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
ActiveCell.Offset(0, -1).Range("A1").Select
Else
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUMIF(INDEX_MONTH,DATE_REVERSION,INDEX_EXIT_CAP)"
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUMIF(INDEX_MONTH,DATE_REVERSION,INDEX_EXIT_CAP)"
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
ActiveCell.Offset(0, -2).Range("A1").Select
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
EndJump:
Worksheets("Assumptions").Calculate
Exit Sub
Application.EnableEvents = True
End Sub
-------------------------------------------------------------------------------------------------
Private Sub Cap_Rate_Change(ByVal Target As Range)
If Intersect(Target, Range("AREA_CAP_RATE_SCHED_SELECT")) Is Nothing Then GoTo EndJump:
If Application.IsError(Application.Match(Target, Worksheets("Lists").Range("LIST_CAP_RATE_SOURCE"), 0)) Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Value = "Manual" Then
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = 0.04
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = 0.04
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
ActiveCell.Offset(0, -1).Range("A1").Select
Else
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUMIF(INDEX_MONTH,DATE_REVERSION,INDEX_EXIT_CAP)"
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUMIF(INDEX_MONTH,DATE_REVERSION,INDEX_EXIT_CAP)"
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
ActiveCell.Offset(0, -2).Range("A1").Select
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
EndJump:
Worksheets("Assumptions").Calculate
Exit Sub
Application.EnableEvents = True
End Sub