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

Answer 2

I modified as below and it is working now. Please advise if this can be improved.

Sub AddRules()

Dim ws As Worksheet
Set ws = Sheets("Report")

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:="=$F4=""NH Orientation""")
        With rule1
            .Interior.Color = RGB(242, 250, 219)
        End With

End With

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

    Set rule2 = .FormatConditions.Add(Type:=xlExpression,Formula1:="=AND($O4<4,$G4=""Elective"")")
        With rule2
            .Interior.Color = RGB(242, 210, 219)
        End With

End With

End Sub

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