公式中的Excel VBA条件格式公式 [英] Excel VBA Conditional Formatting formula within formula

问题描述

我被困住了，我不知道是否有解决方案，因为我尝试了很多并一直失败.

I am stuck and I don't know if there are any solutions to this as I have tried many and kept failing.

我正在尝试添加条件格式，因此如果B列中的单元格等于或小于A列中伙伴单元格的19％，则B列中的单元格会变为黄色.因此，在上面的示例中，B1单元格应该变为黄色，因为$ 19,000少了等于或等于100,000美元的19％.

I am trying to add a conditional formatting so cells in column B turns yellow if it is equal or less than 19% of it's partner cell in column A. So in the example above, cell B1 should turn yellow since $19,000 is less than or equal to 19% of $100,000.

我需要通过excel vba添加此条件格式.我尝试在下面添加vba代码，但B1:B3中所有单元格的条件格式设置公式都被困在 $ A1 * 0.19 中.我需要B1条件格式公式为 $ A1 * 0.19 ，然后B2条件格式公式为 $ A2 * 0.19 ，依此类推.顺便说一下，我大约有350行，而不仅仅是3行.即使如此，我的vba代码也变成了 $ A521325 * 0.19 或与真实或实际相去甚远.

I need to add this conditional formatting through excel vba. I tried adding the vba code below but the conditional formatting formula for all of the cells in B1:B3 get's stuck with $A1*0.19. I need B1 conditional formatting formula to be $A1*0.19, then B2 conditional formatting formula would be $A2*0.19 so on and so fort. I have about 350 rows by the way not just 3. Even so, my vba code becomes $A521325*0.19 or something way off the real or actual.

With Sheet1.Range(Sheet1.Cells(1, 2), Sheet1.Cells(3, 2))
    daformula = "=$A1*0.19"
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:=daformula
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        .FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent2
        .FormatConditions(1).Interior.TintAndShade = -0.249946592608417
    .FormatConditions(1).StopIfTrue = False
End With 

这个想法是在运行将条件格式添加到工作表的宏之后，当用户更改B列中的一个单元格时，颜色会消失或重新出现，具体取决于用户将单元格更改为的值(条件格式必须仍然可以工作)

The idea is after running the macro which adds the conditional formatting to the sheet, when the user changes one of the cells in column B the color either disappear or reappear depending on the value the user changed the cell into (the conditional formatting must still work)

推荐答案

您可以创建一个事件来监视A列中的任何更改(将此代码放置在Sheet1对象中)

You could create an event that monitors any change in column A (place this code in the Sheet1 object)

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then

        'run code to add conditional formatting
        condFormat Target

    End If

End Sub

现在，我们只需要更改您的上面的代码即可接受目标并仅在B列中为等效单元格添加格式

Now we just have to change your above code to accept the Target and only add formatting for the equivalent cell in Column B

Public sub condFormat (Target as range)
    With target.offset(0,1)
        daformula = "=" & target.address & "*0.19"
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
                              Formula1:=daformula
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        .FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent2
        .FormatConditions(1).Interior.TintAndShade = -0.249946592608417
        .FormatConditions(1).StopIfTrue = False
    End With
end sub

我并没有考虑一次更改一个以上的单元格，但这可以解决您的难题

I haven't accounted for changing more than 1 cell at once, but this will solve your challenge

这篇关于公式中的Excel VBA条件格式公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！