条件格式VBA [英] Conditional Formatting VBA

问题描述

我正在建立一个表单来输入帐户信息和订单状态。每一行都需要根据同一行上的一个单元格的值，在这个单元格H上进行更改。我可以使用条件格式轻松实现这一点，但我认为这使得文件比编程代码更大。我已经尝试了一些选择，但我现在可以告诉我失去的方式。我附上一个我想要完成的例子。我不知道该怎么做，所以如果有人可以帮助我，我真的很感激。

---

¹_{事件宏属于工作表或工作簿代码表;不是模块代码表。对于工作表代码表，右键单击工作表的名称选项卡，然后选择查看代码。当VBE打开时，它将在前台具有工作表代码表（通常称为Book1 - Sheet1（Code））。将代码粘贴并进行任何个性化调整，然后点击 Alt + Q 返回工作表。}

I am building a form to enter account information and order status. Each row needs to change based on the value of one of the cells on the same row, on this case cell "H". I can easily achieve this with conditional formatting but I think this makes the file bigger than programming code. I have tried some options but I can tell at this moment I'm way lost. I am attaching an example of what I want to accomplish. I don't know what to do at this point so if someone can help me I would really appreciate it.

enter image description here

解决方案

A Worksheet_Change event macro¹ deals with, well, changes on the worksheet.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("H")) Is Nothing Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        Dim trgt As Range
        For Each trgt In Intersect(Target, Columns("H"))
            Select Case LCase(trgt.Value2)
                Case "credit"
                    Cells(trgt.Row, "A").Resize(1, 12).Interior.ColorIndex = 45
                Case "completed"
                    Cells(trgt.Row, "A").Resize(1, 12).Interior.ColorIndex = 10
                Case Else
                    Cells(trgt.Row, "A").Resize(1, 12).Interior.Pattern = xlNone
            End Select
        Next trgt
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

---

¹ _{An event macro belongs on a Worksheet or Workbook code sheet; not a Module code sheet. For a worksheet code sheet, right click the worksheet's name tab and choose View Code. When the VBE opens, it will have the worksheet code sheet (typically titled something like Book1 - Sheet1 (Code)) in the foreground. Paste the code in and make any personalizing adjustments then tap Alt+Q to return to the worksheet.}

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