Problem with VBA worksheet Function - Runtime Error 1004

[Derek Fingleson]

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 !!

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

 

[Derek Fingleson]

Hi Rory

Tried your solution ... Now get "Runtime error 13 .. Type mismatch"

 

[Derek Fingleson]

Hi Scott

YES YES YES.. It now works
I did a find and replace in the worksheet col B
Removed the RGB and brackets
So just left with eg .. 255,0,255

This Works !!!! ...

Thank you all for responding .. Love you all

 

[Derek Fingleson]

Thank you Fluff

I changed formlae to use the colorIndex values AND THIS ALSO WORKS !!!

Thank you

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Derek Fingleson]

Using Fluff Method

I found this to be Better than using the RGB codes !!!!

Now simply use the ColorIndex number


Here is the Final Answer :-

=======================================


Public Function RandNum()

RandNum = Application.WorksheetFunction.RandBetween(1, 56)


End Function


Sub ChangeBackgourdColorRGB()

Range("E2:E2").Interior.ColorIndex = Application.WorksheetFunction.VLookup(RandNum, Range("A2:B57"), 2, False)
Range("E3:E3").Interior.ColorIndex = Application.WorksheetFunction.VLookup(RandNum, Range("A2:B57"), 2, False)
Range("E4:E4").Interior.ColorIndex = Application.WorksheetFunction.VLookup(RandNum, Range("A2:B57"), 2, False)
Range("E5:E5").Interior.ColorIndex = Application.WorksheetFunction.VLookup(RandNum, Range("A2:B57"), 2, False)
Range("E6:E6").Interior.ColorIndex = Application.WorksheetFunction.VLookup(RandNum, Range("A2:B57"), 2, False)

End Sub

 

[Fluff]

The data in post#5 has the equivalent RGB value next to the colorindex, if that is still the same you can simply use

Range("E2").Interior.ColorIndex = RandNum

and get rid of the need for the table & Vlookup

 

[Derek Fingleson]

Thanks Fluff .. Love you lots

 

[Fluff]

My pleasure