+ Reply to Thread
Results 1 to 7 of 7

Filling colors in random shapes

  1. #1
    Registered User
    Join Date
    11-04-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    85

    Filling colors in random shapes

    There are four transparent shapes on the worksheet myBox1, myBox2, myBox3 and myBox4

    I want to fill yellow color in random shapes based on values in H4 and I4.
    In the attached sample attachment, H4 value is M3. So except the box corresponding to M3, any two of the remaining boxes (1,2 or 4) should be filled with yellow color. Any two becuase, value of I4 is 2.

    Another example:
    Lets say H4 value is M2 and I4 value is 1 then
    any one box should be filled with yellow color except the box corresponding to M2

    The logic what I thought is:
    I would have random numbers to the left of every box (marked in red in the attachment)
    value in H4 would depend who will get a negative random number
    From the remaining, check the two maximum numbers with the LARGE function and identify which box to fill

    Challenge:
    I have tried different ways to do this. However, not getting the syntax right to identify the box. Once the boxes are identified, it will be easier to fill.
    Attached Files Attached Files

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

    Re: Filling colors in random shapes

    Hi freeofcost,
    maybe so
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-04-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    85

    Re: Filling colors in random shapes

    You are simply genius... It will take some time for me to understand the code. Let me have a close look at it. Meanwhile, I have ask something
    I see that you have worked on my logic to create a random number and pick 1 or 2 LARGE numbers out of the 3 selection. I failed in syntax.
    Do you think my logic is efficient enough or is there a better way to do it?

    Also I did not understand the exclamation mark after hide1 in line 2 (hide1!)
    and the ampersand mark after i in line 3 (i&)
    It will be easier for me to comprehend if I understand the reason for above two statements.

    Thank you

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

    Re: Filling colors in random shapes

    this is the abbreviations of variables (& means Long, ! - Single, # - Double, % - Integer, @ - Currency)
    eg Dim x! is the same as Dim x As Single

    $ - As String
    Last edited by nilem; 11-21-2014 at 03:25 AM.

  5. #5
    Registered User
    Join Date
    11-04-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    85

    Re: Filling colors in random shapes

    Quote Originally Posted by nilem View Post
    this is the abbreviations of variables
    Oh! I never came across something like this. Will keep it in mind.

    Sorry if I try to sound stupid.... I wish to understand r(1, 3)... I understood 'r' is the every single element of the defined range (A4,D4,A6,D6). I am confused because not matter the value for Range("I4").Value is 1 or 2, the r(1, 3) always remain constant.
    I am trying to understand this because:
    1. When the cell is colored yellow, i want to set visibility of the shape object over the yellow cell to False temporarily which will be set to True later by another macro
    2. do some activity to change the set random values

    Please help me understand r(1, 3) to set the visibility to False

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

    Re: Filling colors in random shapes

    maybe something like
    Please Login or Register  to view this content.
    about r(1 ,3)
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-04-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    85

    Re: Filling colors in random shapes

    Got it !
    I tried doing the reverse order of TopLeftCell but I was doing it in combination with Application.Caller
    understood the row/column index too

    Thank you !

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Changing colors of shapes based on a click (VBA)
    By domexcel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-15-2014, 10:52 AM
  2. XY Scatter Chart: unique point colors and shapes
    By escobf in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-18-2013, 10:45 AM
  3. Changing Colors of Shapes
    By seckin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-12-2011, 03:23 PM
  4. Colors of shapes and cells
    By mike cook in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-01-2008, 10:59 AM
  5. [SOLVED] VBA code to count colors/shapes?
    By Nimrod in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-08-2005, 01:06 AM

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