Using Conditional Formatting with VBA

[Jones1413]

Hello,

I have a report that I run on a weekly basis. I already have a macro to sort/filter the data. Once the data is sorted I have to manually insert data in Column P. I enter one of the following three things in column P:

• Sponsorship
• Temp-to-Perm
• Standard FTE

Once I enter one of those 3 items in to Column P, I want to create a macro that will fill the entire row a specific color based on what is entered in column P:

• Sponsorship: R: 252, G: 213, B: 180
• Temp-to-Perm: R: 217, G: 217, B: 217
• Standard FTE: Do not fill with anything

Is this possible? The number of rows of data consistently changes so I need it to read to the bottom of the document to find the last row of data. There is a header in row 1 and starts in Column A and runs to Column AN.

Thanks!

 

[onlyadrafter]

Hello,

This code will need to be manually run.

Sub COLOUR_A_AN()
    For MY_ROWS = 2 To Range("P" & Rows.Count).End(xlUp).Row
        Select Case Range("P" & MY_ROWS).Value
            Case "Sponsorship"
                Range("A" & MY_ROWS & ":AN" & MY_ROWS).Interior.Color = RGB(255, 213, 180)
            Case "Temp-to-Perm"
                Range("A" & MY_ROWS & ":AN" & MY_ROWS).Interior.Color = RGB(217, 217, 217)
        End Select
    Next MY_ROWS
End Sub

This code (which needs to go into the relevant sheet code window) will run when data is entered into Column P

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 16 Then
        Select Case Target.Value
            Case "Sponsorship"
                Range("A" & Target.Row & ":AN" & Target.Row).Interior.Color = RGB(255, 213, 180)
            Case "Temp-to-Perm"
                Range("A" & Target.Row & ":AN" & Target.Row).Interior.Color = RGB(217, 217, 217)
        End Select
    End If
End Sub

are either of these of any use?