I’m having problem with my VBA Index/Match for the ‘’’’Detail section color name’’’’ because it refers to another worksheet for the first part of the Match.
The Index/Match for ‘’’’Tab/Cells Color name’’’’ works fine because all the refers to are for the same worksheet it’s on.
This part not working.
Can anyone help with an Index/Match that refers to a second worksheet?
The Index/Match for ‘’’’Tab/Cells Color name’’’’ works fine because all the refers to are for the same worksheet it’s on.
VBA Code:
‘’’’Tab/Cells Color name’’’’
For rowdata = RowDataStart To RowDataEnd
Range("A36:A37").ClearContents
Range("A36").Value = ShGE03.Name & " cell interior color and Tab color is " & _
WorksheetFunction.Index(ShGE03.Range("D5:D" & rowdata), _
WorksheetFunction.Match(ShGE03.Range("A5"), ShGE03.Range("v5:v" & rowdata), 0))
Range("A37").Value = _
WorksheetFunction.Index(ShGE03.Range("H5:H" & rowdata), _
WorksheetFunction.Match(ShGE03.Range("A5"), ShGE03.Range("v5:v" & rowdata), 0)) _
& " and " & _
WorksheetFunction.Index(ShGE03.Range("B5:B" & rowdata), _
WorksheetFunction.Match(ShGE03.Range("A5"), ShGE03.Range("V5:V" & rowdata), 0)) _
& " and Ligtness of " & _
WorksheetFunction.Index(ShGE03.Range("R5:R" & rowdata), _
WorksheetFunction.Match(ShGE03.Range("A5"), ShGE03.Range("V5:V" & rowdata), 0))
NextThis part not working.
VBA Code:
‘’’’Detail section color name’’’’
For rowdata = RowDataStart To RowDataEnd
Dim Sheet1 As Worksheet
Set Sheet1 = Worksheets("General.ColorNames") 'CODENAME ShGE03
Dim Sheet2 As Worksheet
Set Sheet2 = Worksheets("Controls.General") 'CODENAME ShCO01
Range("A38:A39").ClearContents
Range("A38").Value = "All worksheets 'Detail' section cells interior color is " & _
WorksheetFunction.Index(Sheet1.Range("D5:D" & rowdata), _
WorksheetFunction.Match(Sheet2.Range("Q29"), Sheet1.Range("v5:v" & rowdata), 0))
Range("A39").Value = _
WorksheetFunction.Index(Sheet1.Range("H5:H" & rowdata), _
WorksheetFunction.Match(Sheet2.Range("Q29"), Sheet1.Range("v5:v" & rowdata), 0)) _
& " and " & _
WorksheetFunction.Index(Sheet1.Range("B5:B" & rowdata), _
WorksheetFunction.Match(Sheet2.Range("Q29"), Sheet1.Range("V5:V" & rowdata), 0)) _
& " and Ligtness of " & _
WorksheetFunction.Index(Sheet1.Range("R5:R" & rowdata), _
WorksheetFunction.Match(Sheet2.Range("Q29"), Sheet1.Range("V5:V" & rowdata), 0))
NextCan anyone help with an Index/Match that refers to a second worksheet?
