VBA - Conditional formatting based on multiple cell value

[nimesh29]

HI,
I am trying to add conditional formatting to a sheet in the workbook through a macro. I have multiple conditions in columns H & K & P; if column 'H' has certain value then the color of row changes based on the range. Then I want to clear the colored Row if the Column 'K' has either "Approved or Resubmit" or highlight the in Red if the value is "Rejected".


Then once Rejected submittal are returned I need to clear out the Red highlight based on column 'P' value, "Resubmitted".


I got this started but, I am only able to things based on column 'H', don't really know how to add an additional condition from different columns.

Here is the code I have so far:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A2:p100")) Is Nothing Then Exit Sub
    Select Case Cells(Target.Row, "F").Value
        Case "Team1"
            Range(Cells(Target.Row, "A"), Cells(Target.Row, "P")).Interior.ColorIndex = 44
        Case ""
            Range(Cells(Target.Row, "A"), Cells(Target.Row, "P")).Interior.ColorIndex = xlNone
        Case Else
            Range(Cells(Target.Row, "A"), Cells(Target.Row, "P")).Interior.ColorIndex = 6
    End Select
End Sub</code>