harleyberger
New Member
- Joined
- May 1, 2003
- Messages
- 37
Working on a US map that shows media markets. Each media market is a shape and the shape name represents the market name (i.e. Pittsburgh).
I have 2 values for 210 media markets. I want to compare the 2 values for each media market and then fill the corresponding shape with either red or blue based on which value is larger.
I need help with a loop for this instead of writing individual code for each one like I am now....
My media market names are in AH2:AH211 and the corresponding values for each market are in AI2:AJ211.
I'd like the code to look at each media market name, compare the values, and then fill the corresponding shape with either red or blue.
I have 2 values for 210 media markets. I want to compare the 2 values for each media market and then fill the corresponding shape with either red or blue based on which value is larger.
I need help with a loop for this instead of writing individual code for each one like I am now....
Code:
Private Sub Worksheet_Calculate()
If Range("AI2").Value > Range("AJ2").Value Then
ActiveSheet.Shapes.Range(Array("ALBUQUERQUE")).Select
With Selection.ShapeRange.Fill
.ForeColor.RGB = RGB(0, 0, 255)
End With
Else
ActiveSheet.Shapes.Range(Array("ALBUQUERQUE")).Select
With Selection.ShapeRange.Fill
.ForeColor.RGB = RGB(255, 0, 0)
End With
End If
If Range("AI3").Value > Range("AJ3").Value Then
ActiveSheet.Shapes.Range(Array("BOSTON")).Select
With Selection.ShapeRange.Fill
.ForeColor.RGB = RGB(0, 0, 255)
End With
Else
ActiveSheet.Shapes.Range(Array("BOSTON")).Select
With Selection.ShapeRange.Fill
.ForeColor.RGB = RGB(255, 0, 0)
End With
End If
If Range("AI4").Value > Range("AJ4").Value Then
ActiveSheet.Shapes.Range(Array("BUFFALO")).Select
With Selection.ShapeRange.Fill
.ForeColor.RGB = RGB(0, 0, 255)
End With
Else
ActiveSheet.Shapes.Range(Array("BUFFALO")).Select
With Selection.ShapeRange.Fill
.ForeColor.RGB = RGB(255, 0, 0)
End With
End If
End Sub
My media market names are in AH2:AH211 and the corresponding values for each market are in AI2:AJ211.
I'd like the code to look at each media market name, compare the values, and then fill the corresponding shape with either red or blue.