VBA在产品列表中更改单元格颜色 [英] VBA change cell colour in an list of products

问题描述

    嗨

     Hi

所以我有这个产品代码列表，我需要根据代码更改代码所在单元格的颜色。

So I've got this list of product codes and I need to change the colour of the cell the code is in depending on the code.

所以我使用的代码是：

Sub ChangeColor()

    Range("A13").Select
    Do Until IsEmpty(ActiveCell)
        
        If ActiveCell.Text = "100110019" Or "100123015" Then Range(ActiveSheet.ActiveCell).Interior.ColorIndex = 37
        
        
        If ActiveCell.Text = "960225739" Or "960225740" Then Range(ActiveSheet.ActiveCell).Interior.ColorIndex = 15773696
        
        ActiveCell.Offset(1, 0).Select
        
    Loop
End Sub

     

问题 我有是：

The problems I have are:

 它会一直带来错误（对象不支持此属性或方法（错误438）） " Then Range（ActiveSheet.ActiveCell）.Interior。 ColorIndex = 37" 所以任何帮助都会很棒。

 it keeps bring an error (Object doesn’t support this property or method (Error 438)) at "Then Range(ActiveSheet.ActiveCell).Interior.ColorIndex = 37" so any help would be great.

另一个问题是我有成千上万的产品代码需要对它们进行排序，这看起来真的很有效必须在代码中键入每个代码，因为它们可以随时添加更多代码。那么可以在个人的
工作簿中保存备忘单以便查找，我仍然隐藏给用户吗？

And the other issue is I have thousands of product codes and need to sort them all and this seems like a really in efficient way to have to type every one into the code as they can add more at any time. so is it possible to save a cheat sheet on the personal workbook for it to look up, still have I be hidden to the user?

附加信息：

产品代码列表（我需要更改单元格颜色的代码）将定期更改长度，但如果有，则会在新工作表中导出。

the list of product codes(the ones I need to change cell colour) will change length regularly but if it does it will been exported in a new sheet.

我需要细胞在5种不同的颜色之间进行更改，具体取决于它们的含义。

I need the cells to change between 5 different colours depending on what they are.

  ;  任何帮助非常感谢

    Any help greatly appreciated

           -Zues99

           -Zues99

推荐答案

嗨Zues99，

Hi Zues99,

它会有意义的是使用查找表，其中您将产品代码列表作为一种唯一标识符，然后使用具有颜色ID或名称的第二列。

It would make sense to use a lookup table where you have your list of product codes as a kind of unique identifier and then a second column that has the colour ID or name.

PRODUCT_CODE  COLOUR_ID

PRODUCT_CODE  COLOUR_ID

100110019          37

100110019          37

100123015          37

100123015          37

960225739          15773696

960225739          15773696

然后，您可以根据匹配的ID /名称使用vlookup函数返回颜色ID或名称。以下代码经过测试并有效：

You could then return the colour ID or name using the vlookup function based on a matching ID / name. The following code was tested and works:

Sub ChangeColor()
    
    Dim lRow As Long
    
    For lRow = 2 To Range("G65536").End(xlUp).Row 

我的测试中的REM我的产品代码在G
       ;  
       范围（"G"& lRow）.Interior.ColorIndex = Application.WorksheetFunction.VLookup（Range（" A"& lRow）.Value，Range（" A2：B4"），2，False）
       

REM in my test I had product codes in column G                 Range("G" & lRow).Interior.ColorIndex = Application.WorksheetFunction.VLookup(Range("A" & lRow).Value, Range("A2:B4"), 2, False)        

测试表查找表中的REM是A列和A列。 B

REM in test sheet lookup table was column A & B

   下一个lRow
    

End Sub

    Next lRow      End Sub

请参阅以下内容在VBA中使用excel函数的建议

See following for advice in using excel functions in VBA

http://www.excelfunctions.net/ VBA-Vlookup.html

问候

标记

这篇关于VBA在产品列表中更改单元格颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！