Excel使用vba按颜色验证-Excelvalidatebycolorswithvba

Paste the public function into a vba module in your workbook and then use it directly in your worksheet as a formula "getAuth(...)".

将公共函数粘贴到工作簿中的vba模块中,然后直接在工作表中将其用作公式“getAuth(...)”。

Parameters used:

• Letter: Range with the letter

信:范围与信

• Number: Range with the number

数字:带数字的范围

• AuthRange: Range with the Authentication data (the table with the colors)

AuthRange:带有身份验证数据的范围(带颜色的表)

• YesColor: Range with the color used for Yes (uses the background color)

YesColor:使用Yes的颜色范围(使用背景颜色)

Public Function getAuth(Letter As Range, Number As Range, _
AuthRange As Range, YesColor As Range) As String
    Dim rng As Range
    With AuthRange
        Set rng = .Cells(WorksheetFunction.Match(Number, .Columns(1), 0), _
            WorksheetFunction.Match(Letter, .Rows(1), 0))
        getAuth = IIf(rng.Interior.Color = YesColor.Interior.Color, "Yes", "No")
    End With
End Function

You can use something like this directly into your workbook (more details below) :

您可以将这样的内容直接用于您的工作簿(下面有更多详细信息):

Public Function Get_Auth(ByVal User As String, ByVal Product As String) As String
Dim A()
A = ThisWorkbook.Sheets("Feuil1").Range("DB_Prod").Value

For i = LBound(A, 1) To UBound(A, 1)
    For j = LBound(A, 2) To UBound(A, 2)
        If A(i, 1) <> User And A(1, j) <> Product Then
        Else
            'If interior is different of red (use macro recorder to get your own color reference)
            If ThisWorkbook.Sheets("Feuil1").Cells(i, j).Interior.Color <> 255 Then
                'If interior is different of green
                If ThisWorkbook.Sheets("Feuil1").Cells(i, j).Interior.Color <> 5287936 Then
                    'Not handled
                Else
                    Get_Auth = "Yes"
                End If
            Else
                Get_Auth = "No"
            End If
        End If
    Next j
Next i

End Function

First you need to set a name for your authorization table (I used "DB_Prod"), or specify the address of the table ($A$1:$C$10) and change the Sheet name into the code :

首先,您需要为授权表设置一个名称(我使用“DB_Prod”),或者指定表的地址($ A $ 1:$ C $ 10)并将Sheet名称更改为代码:

A = ThisWorkbook.Sheets("Feuil1").Range("DB_Prod").Value

A = ThisWorkbook.Sheets(“Feuil1”)。范围(“DB_Prod”)。值

Then activate macro recorder and change 2 cells color, one to green, one to red to identify the specific colors that you use and change it into the code :

然后激活宏录制器并更改2个单元格颜色,一个更改为绿色,一个更改为红色以标识您使用的特定颜色并将其更改为代码:

If ThisWorkbook.Sheets("Feuil1").Cells(i, j).Interior.Color <> 5287936 (green color)

如果ThisWorkbook.Sheets(“Feuil1”)。Cells(i,j).Interior.Color <> 5287936(绿色)

Then you can type directly into your workbook : =Get_Auth(A2,C1)

然后你可以直接输入你的工作簿:= Get_Auth(A2,C1)

Enjoy!