excel - 通过比较动态值的条件格式：Excel

问题描述

我的代码：

=IF(AND(B2="Lodavan-Boleropickup",C2<"30"),CELL("color",D2),"")

它没有用。

请参考下表。我需要突出显示“里程”列下的单元格。

例子：

“Lodavan-Boleropickup”（目标里程为 30）我需要突出显示“里程”列中低于 44 的单元格。“Suzuki-Fabric”（目标里程为 50）我需要突出显示“里程”列中的单元格低于55。

条件是它必须参考“车辆名称”列然后检查值“里程”，然后它必须与目标值进行比较然后突出显示。任何可能的简单方法都可以。

或者比较预定义的表，如：

我的要求：如果“车辆名称”列中的变量是“Loadvan-Boleropicup”，那么它必须将“里程列”中的值与我们需要定义的“目标”进行比较，然后它必须突出显示单元格。

标签： excelexcel-formulaexcel-2010excel-2007

可能这两种解决方案可以帮助您：

解决方案1）在第一个表中插入一个名为的列selected_names，并将此公式写入第一个单元格初始化并填写以进行复制。 =SetColor(IF(INDEX($C$2:$C$12;MATCH(F2;$B$2:$B$12;0))>G2;F2;"");IF(INDEX($C$2:$C$12;MATCH(F2;$B$2:$B$12;0))>G2;F2;""))

你需要在 VBA 编辑器中编写这个函数：

    Option Explicit

    Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Declare PtrSafe Function GlobalAddAtom Lib "kernel32" Alias "GlobalAddAtomA" (ByVal lpString As String) As Integer
    Declare PtrSafe Function GlobalGetAtomName Lib "kernel32" Alias "GlobalGetAtomNameA" (ByVal nAtom As Integer, ByVal lpBuffer As String, ByVal nSize As Long) As Long

Public Function SetColor(ByVal Value As Variant, ByVal BackGroundColor As String) As Variant
    SetTimer Application.hwnd, GlobalAddAtom(Application.Caller.Address(External:=True) & "*" & BackGroundColor), 0, AddressOf ChangeColor
    SetColor = Value
End Function

Sub ChangeColor(ByVal hwnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
    Dim sBuffer As String, lRet As Long, lColorIndex As Long

    On Error Resume Next
    KillTimer hwnd, nIDEvent
    sBuffer = Space(256)
    lRet = GlobalGetAtomName(nIDEvent, sBuffer, Len(sBuffer))
    sBuffer = Left(sBuffer, lRet)
    Select Case LCase(Split(sBuffer, "*")(1))
        Case "name1"
            lColorIndex = 17
        Case "name2"
            lColorIndex = 18
        Case "name3"
            lColorIndex = 19
        Case "name4"
            lColorIndex = 20
        Case "name5"
            lColorIndex = 21
        Case "name6"
            lColorIndex = 22
        Case "name7"
            lColorIndex = 23
        Case "name8"
            lColorIndex = 24
        Case "name9"
            lColorIndex = 25
        Case "name10"
            lColorIndex = 26
        Case "name11"
            lColorIndex = 27
        Case ""
            lColorIndex = xlColorIndexNone
    End Select
    Range(Split(sBuffer, "*")(0)).Interior.colorIndex = lColorIndex
End Sub

设置单元格颜色的参考链接：https ://www.mrexcel.com/board/threads/change-cell-color-inside-formula.1050178/

解决方案2）您可以在第一个单元格中插入列selected_names并使用此公式并向下填充以复制到所有单元格。 =IF(INDEX($C$2:$C$12;MATCH(F2;$B$2:$B$12;0))>G2;F2;"") 所有条件行都将被标记，然后您可以按相似的名称过滤它们，并且不需要 VBA 函数来为匹配名称着色（这种方式在我的想法中更好）。

excel - 通过比较动态值的条件格式：Excel

问题描述

解决方案

推荐阅读