Derek Fingleson
New Member
- Joined
- Jan 22, 2020
- Messages
- 16
- Office Version
- 365
- 2013
- Platform
- Windows
Hi Guys
I have a simple worksheet.
Col A - Numbers from A1 to A56 ... Numbers 1 to 56
Col B - Rows B1 to B56 .. The RGB Color eg..RGB(255,0,255) .. Different rgb codes for all 56 rows
Col C - Blank
Col D - Rows 2, 3, 4 ,5 ,6 .. Simply have a different letter in each
What I'm trying to achieve is simple ..
1. Run the Macro
.. Each time you run it , it allocates a random number between 1 and 56
.. Vlookup then gets that number , finds it in Col A , Returns the value in Col B
... Then the Cell Colors in Col D2:D6 keep changing randomly when macro run
SO THIS IS WHAT I GOT IN MY VBA SO FAR .. But its not working:-
Please help a Bud out here !!
I have a simple worksheet.
Col A - Numbers from A1 to A56 ... Numbers 1 to 56
Col B - Rows B1 to B56 .. The RGB Color eg..RGB(255,0,255) .. Different rgb codes for all 56 rows
Col C - Blank
Col D - Rows 2, 3, 4 ,5 ,6 .. Simply have a different letter in each
What I'm trying to achieve is simple ..
1. Run the Macro
.. Each time you run it , it allocates a random number between 1 and 56
.. Vlookup then gets that number , finds it in Col A , Returns the value in Col B
... Then the Cell Colors in Col D2:D6 keep changing randomly when macro run
SO THIS IS WHAT I GOT IN MY VBA SO FAR .. But its not working:-
Please help a Bud out here !!
VBA Code:
Public Function RandNum()
RandNum = Application.WorksheetFunction.RandBetween(1, 56)
End Function
Sub ChangeBackgourdColorRGB_Range()
Range("D2:D2").Interior.Color = Application.WorksheetFunction.VLookup("RandNum", "A2:B57", 2, False)
Range("D3:D3").Interior.Color = Application.WorksheetFunction.VLookup("RandNum", "A2:B57", 2, False)
Range("D4:D4").Interior.Color = Application.WorksheetFunction.VLookup("RandNum", "A2:B57", 2, False)
Range("D5:D5").Interior.Color = Application.WorksheetFunction.VLookup("RandNum", "A2:B57", 2, False)
Range("D6:D6").Interior.Color = Application.WorksheetFunction.VLookup("RandNum", "A2:B57", 2, False)
End Sub