Random fill color macro

I Googled around and cobbled a macro to generate a random fill color. I pre-select the range I want colored, and it fills that range with the supposedly random number using the RGB naming convention. It has worked very well on most of my spreadsheets except for one. In this case I have used a variety of color fills to separate different types of information - and for fun. However, it will select a color almost every time that is somewhere used on this sheet. I may have a total of 20 or so different colors - no where near the 255 x 255 x 255 = 16 million or so possibilities. I have not seen how far it will go in repeating pre-used colors but I have had it do so 10 times in a row before i quit trying. I am not using any conditional formatting on this sheet. Here is the code: '

Please Login or Register  to view this content.

The lines I've "remarked out" were abortive attempts to foil the color matching gremlin. I've watched the three rand variables as they are generated one by one to see if i could possibly tell that they were being generated based on previously used rgb codes already in use - but I saw nothing unusual other than the code ended up being one that has already been used on this spreadsheet.

This is not life-threatening, but I sure am curious and willing to accept shame and remorse at overlooking something simple - if one of you can tell me what is going on. I have seen no repetitious color selecting on virgin sheets.

Thanks in advance.

Korndorb

Hi Korndorb,

See if using (putting) the Randomize function just beneath where you declare your variables does the trick.

Regards,

Robert

Robert, here is my code without all the "remarked out stuff" - just the minimal code being used:

Please Login or Register  to view this content.

How can put the rand1 =..., rand2 = .... any closer to the variable declarations? I assume I understand your suggestion and that the rand1= etc., three statements are the "Randomize function", and that the dimension statements is where the variables are declared.

Thanks Bob K.

Hi Bob,

Try this:

Please Login or Register  to view this content.

HTH

Robert

Why did that fix it

Randomize forces the Rnd function to start afresh whereas without it Rnd always starts at the same number. See here for more info.

If you could mark the thread as closed it would be appreciated.

Regards,

Robert

Hi Bob K,

Administrative Note:

• We would love to continue to help you with your query, but first, before we can proceed…
• Please see Forum Rule #3 about the proper use of code tags and adjust accordingly...
• Added for you this time, but please use them in the future…Thanks.