I have the following VBA code which was originally utilized for Excel 2003 and updated for Excel 2007.
The code changes from sad face & red color to happy face & green color on each of 50 Smiley Faces on a USA map, indicating the sales rep has sold in that state.
The problem is my client is now using Excel 2010 and the VBA code is giving an error when an "X" is placed in Range("C7:C57").
I have rechecked the code in Excel 2007 and it functions perfectly.
I am completely stumped why this VBA code is causing a error.
I think the VBA code might be utilizing outdated statements for Excel 2010.
I would greatly appreciate feedback on how I can modify the VBA code to be compatible with Excel 2010
The code changes from sad face & red color to happy face & green color on each of 50 Smiley Faces on a USA map, indicating the sales rep has sold in that state.
The problem is my client is now using Excel 2010 and the VBA code is giving an error when an "X" is placed in Range("C7:C57").
I have rechecked the code in Excel 2007 and it functions perfectly.
I am completely stumped why this VBA code is causing a error.
I think the VBA code might be utilizing outdated statements for Excel 2010.
I would greatly appreciate feedback on how I can modify the VBA code to be compatible with Excel 2010
Code:
'Change Smiley face Red / Green depending if "X" is in Upsell cell for State
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C7:C57")) Is Nothing Then
Dim rgCell As Range
For Each rgCell In Target
If rgCell.Value = "" Then
With Worksheets("Map").Shapes("AutoShape " & _
Worksheets("Map").Range("C" & rgCell.Row + 64).Value)
.Fill.ForeColor.SchemeColor = 10 'Red
.Adjustments.Item(1) = -0.04653 'sad face for Excel 2007
'.Adjustments.Item(1) = 0.7181 'sad face for Excel 2003
End With
Else: With Worksheets("Map").Shapes("AutoShape " & _
Worksheets("Map").Range("C" & rgCell.Row + 64).Value)
.Fill.ForeColor.SchemeColor = 11 'Green
.Adjustments.Item(1) = 0.04653 'happy face for Excel 2007
'.Adjustments.Item(1) = 0.8111 'happy face for Excel 2003
End With
End If
Next rgCell
End If
End Sub