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

 

[RoryA]

You're currently looking up the text "RandNum" but you should be looking up the result of the function. Change this line:

Application.WorksheetFunction.VLookup("RandNum", "A2:B57", 2, False)

to this:

Application.WorksheetFunction.VLookup(RandNum, "A2:B57", 2, False)

in each case.

 

[Derek Fingleson]

Hi Rory

Have tried that already , as well

Still getting " Run-time error '1004' Unable to get the Vlookup property of the WorksheetFunction class "

 

[Scott T]

Try

Sub ChangeBackgourdColorRGB()

Range("D2:D2").Interior.Color = Application.WorksheetFunction.VLookup(Application.WorksheetFunction.RandBetween(1, 56), Range("A2:B57"), 2, False)
Range("D3:D3").Interior.Color = Application.WorksheetFunction.VLookup(Application.WorksheetFunction.RandBetween(1, 56), Range("A2:B57"), 2, False)
Range("D4:D4").Interior.Color = Application.WorksheetFunction.VLookup(Application.WorksheetFunction.RandBetween(1, 56), Range("A2:B57"), 2, False)
Range("D5:D5").Interior.Color = Application.WorksheetFunction.VLookup(Application.WorksheetFunction.RandBetween(1, 56), Range("A2:B57"), 2, False)
Range("D6:D6").Interior.Color = Application.WorksheetFunction.VLookup(Application.WorksheetFunction.RandBetween(1, 56), Range("A2:B57"), 2, False)

End Sub

 

[Derek Fingleson]

ColorIndex	Excel VBA Color
1	RGB(0,0,0)		A
2	RGB(255,255,255)		B
3	RGB(255,0,0)		C
4	RGB(0,255,0)		D
5	RGB(0,0,255)		E
6	RGB(255,255,0)
7	RGB(255,0,255)
8	RGB(0,255,255)
9	RGB(128,0,0)
10	RGB(0,128,0)
11	RGB(0,0,128)
12	RGB(128,128,0)
13	RGB(128,0,128)
14	RGB(0,128,128)
15	RGB(192,192,192)
16	RGB(128,128,128)
17	RGB(153,153,255)
18	RGB(153,51,102)
19	RGB(255,255,204)
20	RGB(204,255,255)

 

[Derek Fingleson]

Hi Scott

Tried yours also..
Now Getting : "Run-time error '13' : Type mismatch

 

[Derek Fingleson]

My post above is of the Excel Sheet ( First 20 rows) .. Colums A,B,C,D

 

[RoryA]

Apologies - I misread your code and didn't see you were missing a range call too:

Application.WorksheetFunction.VLookup(RandNum, Range("A2:B57"), 2, False)

 

[Fluff]

You cannot set the interior colour like that.
The values in col B are just strings, so you either need to split the string into it's individual elements, or just put a number in the cell instead.
With the sample date you have shown the RGB values are the same as the colorIndex values, so you could just use that instead.

 

[Scott T]

Because you have RGB in the cell it is not a valid color you are looking up RGB(0,0,250) not 0,0,250. Do you have to have the RGB in the cell?

This works with the code I posted

Book1
	A	B
2	1	255,0,255
3	2	254,0,255
Sheet4