CODEWITHSUNDEEP
databaseexcelvbaformattinguserform
rogiefc
rogiefc

Reputation: 13

Conditional Formatting VBA

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

Upvotes: 0

Views: 95

Answers (1)

user4039065
user4039065

Reputation:

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

H_credit_completed

¹ 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.

Upvotes: 2

Related Questions