Solved

Select a random value from list and chg background color.

Posted on 2016-10-10
5
47 Views
Last Modified: 2016-10-10
I have a sheet tab, Sheet1 and in column A has values in it.  I would like to (no matter how many values are there) press a button on the sheet that selects a random value from that column and pops it up in a Msgbox.  It would highlight that value, so the next time it does a random select it will exclude any that have the background color.  So the background colored cell would accumulate, and eventually all in the list will have a background color.  When I do and clear all background colors it will start over.
0
Comment
Question by:RWayneH
  • 3
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41836843
Hi,

pls try

Sub Macro()
Dim aRows() As Variant
Set myRng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
myRng.AutoFilter Field:=1, Operator:=xlFilterNoFill

If myRng.SpecialCells(xlCellTypeVisible).Cells.CountLarge = 1 Then
    myRng.AutoFilter
    Range("A1").EntireColumn.Interior.Pattern = xlNone
Else
    Set rngSpec = myRng.Offset(1).Resize(myRng.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
    Idx = 0
    For Each c In rngSpec
        ReDim Preserve aRows(Idx)
        aRows(Idx) = c.Row
        Idx = Idx + 1
    Next
    If rngSpec.Cells.Count >= 1 Then
        LngValCnt = rngSpec.Cells.Count
        res = WorksheetFunction.RandBetween(0, UBound(aRows))
        Cells(aRows(res), 1).Interior.Color = 65535

    End If
    myRng.AutoFilter
    MsgBox Cells(aRows(res), 1).Value
End If
End Sub

Open in new window

Regards
EE20161010.xlsm
0
 

Author Comment

by:RWayneH
ID: 41836886
Working good and thanks.,,,  Does it matter that I do not have a column header a ColA?  If there is an edit required which Ln would need to chg?
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41836916
Since I use Filter it is necessary to have a header
0
 

Author Closing Comment

by:RWayneH
ID: 41836930
Excellent!!!  Thanks for the help
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41836960
Version without filter (no header)

Sub Macro1()
Dim aRows() As Long
Set myRng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
For Each c In myRng
    If c.Interior.Pattern = xlNone Then
        ReDim Preserve aRows(Idx)
        aRows(Idx) = c.Row
        Idx = Idx + 1
        bInit = True
    End If
Next
If bInit = False Then
    Range("A1").EntireColumn.Interior.Pattern = xlNone
Else
    res = WorksheetFunction.RandBetween(0, UBound(aRows))
    Cells(aRows(res), 1).Interior.Color = 65535
End If
End Sub

Open in new window

0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Building a website can seem like a daunting task to the uninitiated but it really only requires knowledge of two basic languages: HTML and CSS.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question