conditional formatting autoshapes

  • Thread starter Thread starter parachutester
  • Start date Start date
P

parachutester

I have a cluster of autoshapes (map of a city divided into regions)
that I need to colour according to how much that region spent on
services. I have a column with the region names, a column with how
much spending was predicted and a column with the real spend. I have
named each autoshape after the region, but am stuck now. I need the
autoshapes to show up as green if they spent within their budget or red
if not. Am pretty new to VBA, but can work with it. Many thanks for
all help.
 
You're not going to be able to do it with conditional formatting (to
the best of my knowledge). You could do it with VBA using code such as
follows:

Const NUMREGIONS As Integer = 5

Public Sub ShadeRegions()

Dim ws As Worksheet
Dim arrRegionCosts(NUMREGIONS) As Variant
Dim arrRegionShapes(NUMREGIONS) As Shape
Dim i As Integer

Set ws = Application.Sheets("YOUR SHEET NAME")

Set arrRegionShapes(0) = ws.Shapes("AutoShape 1")
Set arrRegionShapes(1) = ws.Shapes("Autoshape 2")
Set arrRegionShapes(2) = ws.Shapes("Autoshape 3")
Set arrRegionShapes(3) = ws.Shapes("Autoshape 4")
Set arrRegionShapes(4) = ws.Shapes("Autoshape 5")

arrRegionCosts = ws.Range("A1:A5").Value
For i = 1 To NUMREGIONS
If arrRegionCosts(i, 1) > 40 Then
arrRegionShapes(i - 1).Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf arrRegionCosts(i, 1) > 30 Then
arrRegionShapes(i - 1).Fill.ForeColor.RGB = RGB(255, 64,
64)
ElseIf arrRegionCosts(i, 1) > 20 Then
arrRegionShapes(i - 1).Fill.ForeColor.RGB = RGB(255, 128,
128)
ElseIf arrRegionCosts(i, 1) > 10 Then
arrRegionShapes(i - 1).Fill.ForeColor.RGB = RGB(255, 192,
192)
Else
arrRegionShapes(i - 1).Fill.ForeColor.RGB = RGB(255, 255,
255)
End If
Next

End Sub


There are a couple of quirky points I should make about the code above.
When setting an array from a range, as follows ...
arrRegionCosts = ws.Range("A1:A5").Value

.... (1) the array created is has a base of 1 (not 0) and (2) it is 2D
by default, hence the arrRegionCosts(i, 1) subscripts.

HTH,

Nick Hebb
BreezeTree Software
http://www.breezetree.com


2.
 
Many thanks for this. I have manipulated the code for my purposes, but
am coming up with "cant assign to array" at the following line in the
code:

arrRegionCosts = ws.Range("DB2:DB35").Value

Have not been able to get around this one.
 
Back
Top