Copy last formatted cell into another cell in the sheet

[MikeyL75]

Hi,

This sheet is to perform a raffle, with people's names assigned 1 to 100 in the range TargetRg. It colors random cells orange as a build up, then selects a last random cell and colors it green.

The code I'm currently using is below. It works exactly how I want it to, until the last part. I want the last random cell that the code colors to then be copied to D17. I've tried various methods online and nothing seems to be working.

Help please!

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Function RandCell(Rg As Range) As Range
    Set RandCell = Rg.Cells(Int(Rnd * Rg.Cells.Count) + 1)
End Function


Sub RandCellTest()
Dim Counter As Long
Dim TargetRg As Range, Cell As Range

Set TargetRg = Range("B5:K14")

    TargetRg.Interior.Color = RGB(255, 255, 255)

For Counter = 1 To 50
    Set Cell = RandCell(TargetRg)
    Cell.Interior.Color = RGB(255, 165, 0)
    'Cell.Interior.Color = RGB(Int((255 * Rnd)), Int((255 * Rnd)), Int((255 * Rnd)))   'if I want a random color
    Sleep (250)
    Cell.Interior.Color = xlColorIndexNone
Next
    Set Cell = RandCell(TargetRg)
    Cell.Interior.Color = RGB(34, 139, 34)
        
    Worksheets("Generator").Range("D17").Value = ActiveCell.Value   'need this to copy the cell colored above, and paste it to D17.
        
End Sub

 

[MickG]

Perhaps ???:-

Worksheets("Generator").Range ("D17").Value = Cell.Value

 

[jmacleary]

Hi there. With your code as it is, the activecell is not the one youve just coloured. Try modifying the last line to read:

    Worksheets("Generator").Range("D17").Value = Cell.Value   'need this to copy the cell colored above, and paste it to D17.

 

[MikeyL75]

That worked perfectly, thanks both.

Another issue I appear to be running into, it seems the cell that is selected at the end of the counter always ends up the same. Is there a way to further randomise it?

Thanks in advance

 

[jmacleary]

I'm not sure why - in my copy of the code it returns a random one each time.

 

[MikeyL75]

Sorry, I should clarify. It does return a random one each time, however it always follows the same pattern after I run it from opening the sheet. Cell 93 first, then Cell 75, 79 etc.

 

[jmacleary]

OK, sorry. Put a Randomize statement in your rand routine like this:

Function RandCell(Rg As Range) As Range
Randomize
Set RandCell = Rg.Cells(Int(Rnd * Rg.Cells.Count) + 1)
End Function

 

[MikeyL75]

Perfect, just what I wanted. Thanks for you help :D