I have the following formula (which works fine) on a spreadsheet that I’m changing into VBA.
the formula gives me RGB(204,255,145)
But when I add beginning text or separating commas or a ending ) i get nothing.
This is a sample of my last failure.
ShGE04 is the CodeName for worksheet ColorList.
ShGE02 is the CodeName for worksheet Office.
If I ‘test’ one of those WorksheetFunction.Index/Match it will give me the correct number, it’s when I try to combine them that will not work.
Other worksheet code locations I have no problem adding text or special characters. But those are single Index/Match formulas where this problem has 3.
Can someone help?
VBA Code:
="RGB("&INDEX(ColorList!$C$5:$C$1000,MATCH(Office!$A$2,ColorList!$F$5:$F$1000))&","&INDEX(ColorList!$D$5:$D$1000,MATCH(Office!$A$2,ColorList!$F$5:$F$1000))&","&INDEX(ColorList!$E$5:$E$1000,MATCH(Office!$A2,ColorList!$F$5:$F$1000))&")"
the formula gives me RGB(204,255,145)
But when I add beginning text or separating commas or a ending ) i get nothing.
This is a sample of my last failure.
VBA Code:
"RGB(" & WorksheetFunction.Index(ShGE04.Range("C5:C1000"), WorksheetFunction.Match(ShGE02.Range("A2"), ShGE04.Range("F5:F1000"), 0)) & "," &
WorksheetFunction.Index(ShGE04.Range("D5:D1000"), WorksheetFunction.Match(ShGE02.Range("A2"), ShGE04.Range("F5:F1000"), 0)) & "," &
WorksheetFunction.Index(ShGE04.Range("E5:E1000"), WorksheetFunction.Match(ShGE02.Range("A2"), ShGE04.Range("F5:F1000"), 0)) & ")"
ShGE04 is the CodeName for worksheet ColorList.
ShGE02 is the CodeName for worksheet Office.
If I ‘test’ one of those WorksheetFunction.Index/Match it will give me the correct number, it’s when I try to combine them that will not work.
Other worksheet code locations I have no problem adding text or special characters. But those are single Index/Match formulas where this problem has 3.
Can someone help?