Home |
Search |
Today's Posts |
#1
|
|||
|
|||
conditional formatting autoshapes
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
conditional formatting question | Excel Discussion (Misc queries) | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |