Solved

Select a random value from list and chg background color.

Posted on 2016-10-10
5
49 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
.CSS HTML Help 3 28
Check input text, Number 7 36
Macro problems with Excel file 6 27
Slicers by Groups in Excel 7 21
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Originally, this post was published on Monitis Blog, you can check it here . Websites are getting bigger and more complicated by the day. Video, images and custom fonts are all great for showcasing your product or service. But the price to pay in…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

726 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