Author Topic: Conditional formatting  (Read 3062 times)

0 Members and 1 Guest are viewing this topic.

Andy_E

  • Newbie
  • *
  • Posts: 1
Conditional formatting
« on: December 22, 2008, 02:37:44 PM »

Hi all,

I'm new to visio but need to learn quickly - in the new year I need to create lots of org charts fairly quickly from excel data. I've prepared the excel data and can get the org charts drawn from the wizzard but I need to colour code each position.

Example excel data attached to ilustrate:

Fill colour - to represent function

Outline colour - to represent team

I've tried a few formulas in ShapeSheet but can't get it to work and help would be greatly appreciated

Andy

joemako

  • Jr. Member
  • **
  • Posts: 40
    • Joe Mako
Re: Conditional formatting
« Reply #1 on: December 22, 2008, 06:27:00 PM »
your mileage may vary with this, but you can try something like this:

Code
Sub SetLineAndFill()

    Dim strTeam As String
    Dim strFunction As String

    Dim shp As Visio.Shape

    For Each shp In Application.ActiveWindow.Page.Shapes

        If shp.Type = 2 Then
            'this is a group shape

            'get team and function values and strip off quotes
            strTeam = shp.Cells("Prop.Team.Value").Formula
            strFunction = shp.Cells("Prop.Function.Value").Formula
           
            strTeam = Mid(strTeam, 2, Len(strTeam) - 2)
            strFunction = Mid(strFunction, 2, Len(strFunction) - 2)
           
           
            Select Case strTeam
                Case "A"
                    shp.Cells("LineColor") = "2" 'Red
                Case "B"
                    shp.Cells("LineColor") = "3" 'Green
                Case "X"
                    shp.Cells("LineColor") = "4" 'Blue
                Case Else
                    'Do Nothing
            End Select
           
            Select Case strFunction
                Case "Management"
                    shp.Cells("FillForegnd") = "2" 'Red
                Case "Supervisor"
                    shp.Cells("FillForegnd") = "3" 'Green
                Case "Worker"
                    shp.Cells("FillForegnd") = "4" 'Blue
                Case "Admin"
                    shp.Cells("FillForegnd") = "5" 'Yellow
                Case "Data processor"
                    shp.Cells("FillForegnd") = "6" 'Pink
                Case Else
                    'Do Nothing
            End Select
           
        End If

    Next

End Sub