Excel Conditional Format

Question

Excel Conditional Format

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

Good morning, I am creating a spreadsheet with a form to be able to document the status of an order. Depending on the status, the cells from A to N have to change color, additionally for certain status such as "CI ERROR / TICKET", the color of the letter has to change to white and bold. I could already manage to change the color in the range of cells mentioned (A, N), what I can not do is change the color and style of the font in the same code. Additionally I am trying to do a checklist in the boxes (O, Z) and should be able to change the color of each cell depending on value: "Y", "NA" and "P". Honestly come to the end of my capabilities, this is what I have of code in the attributes of the Excel sheet:

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 "2 day process"
                Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 46
                Case "advisor"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                Case "back in"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 22
                Case "ci error/ticket"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 1
                    Cells(trgt.Row, "A").Resize(1, 14).Font.ColorIndex = 1
                    Cells(trgt.Row, "A").Resize(1, 14).Font.FontStyle = "Bold"
                Case "completed"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 10
                Case "completed/backup"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 51
                    Cells(trgt.Row, "A").Resize(1, 14).Font.ColorIndex = 1
                    Cells(trgt.Row, "A").Resize(1, 14).Font.FontStyle = "Bold"
                Case "credentialing"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 49
                    Cells(trgt.Row, "A").Resize(1, 14).Font.ColorIndex = 1
                    Cells(trgt.Row, "A").Resize(1, 14).Font.FontStyle = "Bold"
                Case "credit"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 44
                Case "duplicate"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 10
                Case "held"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                Case "master data"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                Case "name change"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                Case "ofr"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 3
                Case "op consultant"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                Case "post process"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 32
                Case "pps"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                Case "react acct"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                Case "rejected"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 10
                Case "transferred"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 10
                Case "zpnd"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                Case Else
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.Pattern = xlNone
        End Select
    Next trgt
End If bm_Safe_Exit:
Application.EnableEvents = True
End Sub

vba formularios excel

asked by rogiefc 13.10.2016 в 04:50

source

2 answers

Custom decode for JSON with PHP Apply plugin Select2 with vue.js

score 1 · Answer 1

Personally, to change the colors of a cell according to its content, I would use the formato condicional , it allows you to use conditionals equally, and if it is fulfilled, the source format that you indicate on that line is applied.

To copy this "code" of conditional format to another cell that shares the same conditions, the option copiar formato of Excel is used.

This way you will find the code more portable, as well as being able to apply it to many different cells without having to change the VBS code below.

link

score 0 · Answer 2

To answer this issue you can do the following:

For the style of the source .Font.Bold = True

For the font color .Font.Color = RGB(255, 255, 255) color White

For the cell color .Interior.Color = RGB(255, 0, 0)

in this link you will find the RGB parameters for the colors.

On the other hand I recommend "factoring" Cells(trgt.row, "A").Resize(1, 14) using with makes the code more readable.

With Cells(trgt.row, "A").Resize(1, 14)
    Select Case LCase(trgt.Value2)
        Case "2 day process"
            .Interior.ColorIndex = 46
        Case "advisor"
            .Interior.ColorIndex = 37
        Case "back in"
            .Interior.ColorIndex = 22
        Case "ci error/ticket"
            .Interior.ColorIndex = 1
            .Font.ColorIndex = 1
            .Font.FontStyle = "Bold"
    ...
End With