excel - Translating Conditional Formatting to VBA codes

Question

I have to apply two conditional formatting to my data in a sheet called "Report" (num of rows are not fixed). I can do these via the "Manage Rules" option via conditional formatting. So I tried to record macro but unfortunately I don't see any codes recorded.

Conditional Formatting 1:

 =$F5="NH Orientation" , then Color (242,220,219)

Conditional Formatting 2:

=OR($O4<4,$G4="Elective"), then color (242,220,219)

Post which I will cut and paste the colored cells in row 2 and below in another sheet called "Removed"

I want to have these conditions in macro in my excel.

Answer 1

You decide how to tweak but the following are the main elements:

Option Explicit

Public Sub AddRules()

    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = ThisWorkbook.Worksheets("Report")   ' change

    Dim rule1 As FormatCondition
    Dim rule2 As FormatCondition

    Dim lastRow As Long

    lastRow = GetLastRow(ws, 1)

    If lastRow < 4 Then
        MsgBox "Invalid number of rows"
        Exit Sub
    End If

    With ws.Range("A4:V" & lastRow)

        .FormatConditions.Delete

        Set rule1 = .FormatConditions.Add(Type:=xlExpression, _
                                          Formula1:="=$F5=""NH Orientation""")
        rule1.StopIfTrue = True 'Change as required

        Set rule2 = .FormatConditions.Add(Type:=xlExpression, _
                                          Formula1:="=OR($O4<4,$G4=""Elective"")")
        Dim i As Long

        For i = 1 To 2

            With .FormatConditions(i)

                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = RGB(242, 220, 219)
                    .TintAndShade = 0
                End With

            End With

        Next i

    End With

End Sub
Public Function GetLastRow(ByVal ws As Worksheet, Optional ByVal columnNumber As Long = 1) As Long

    With ws

       GetLastRow = .Cells(.Rows.Count, columnNumber).End(xlUp).Row

    End With

End Function

Reference:

1. https://msdn.microsoft.com/en-us/vba/excel-vba/articles/formatconditions-add-method-excel