+ Reply to Thread
Results 1 to 4 of 4

Need help cleaning up already working code involving conditional shape fill

Hybrid View

  1. #1
    Registered User
    Join Date
    01-27-2008
    Posts
    8

    Need help cleaning up already working code involving conditional shape fill

    First of all, thanks to the community as using the forum search I've already got my basic code working. Now I'm looking for some guidance as to how to clean up what I've done already.

    I have a drawing with autoshapes representing parts that need to be inspected. The important data in my table consists of the part number, and a dropdown box where I can select OK, WATCH, or CHANGE. Right now I have this macro that successfully changes the color of the part on the map based on the dropdown box.

    If Sheets("date last scanned").Range("H4") = "CHANGE" Then
        Sheets("roll map").Shapes("#01").Fill.ForeColor.RGB = RGB(255, 0, 0)
    End If
    
    If Sheets("date last scanned").Range("H4") = "OK" Then
        Sheets("roll map").Shapes("#01").Fill.ForeColor.RGB = RGB(0, 175, 80)
    End If
    
    If Sheets("date last scanned").Range("H4") = "WATCH" Then
        Sheets("roll map").Shapes("#01").Fill.ForeColor.RGB = RGB(233, 238, 34)
    End If
    So, this is working. Unfortunately I have 50 parts and 50 shapes that need the same treatment. Now if I just wanted to be inefficient and clunky I could just copy the above 50 times and rename by hand, but there's sleeker ways of doing it that I haven't grasped yet. I'm hoping you fine folks will help me out and save me some tedious work that would be in my future.

    Info that may help:
    -currently my shapes are named #01, #02, etc but that can be changed if it makes it easier
    -the cells are in order starting from H4 (#01 for H4, #02 for H5, etc)

    Thanks in advance

  2. #2
    Registered User
    Join Date
    01-27-2008
    Posts
    8

    Re: Need help cleaning up already working code involving conditional shape fill

    bump to hopefully catch more of the morning folk

  3. #3
    Registered User
    Join Date
    01-27-2008
    Posts
    8

    Re: Need help cleaning up already working code involving conditional shape fill

    Do I need to include more information or something? I tried to include everything I thought was relevant but not sure what may be needed.

  4. #4
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Need help cleaning up already working code involving conditional shape fill

    maybe something like this
    Private Sub Worksheet_Change(ByVal Target As Range) ' in sheet "date last scanned" module
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("H4:H53")) Is Nothing Then Exit Sub
    Dim i&: i = Target.Row - 3
    With Sheets("roll map")
        Select Case Target.Value
            Case "CHANGE": .Shapes("#0" & i).Fill.ForeColor.RGB = RGB(255, 0, 0)
            Case "OK": .Shapes("#0" & i).Fill.ForeColor.RGB = RGB(0, 175, 80)
            Case "WATCH": .Shapes("#0" & i).Fill.ForeColor.RGB = RGB(233, 238, 34)
        End Select
    End With
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1