Excel Macro to apply conditional formatting

Question

This is my first Macro project and I am trying to figure it out how to fix it. I want to have yellow highlighted to the same column in range but after run highlighted all in range. Any insight for me?

this is how I want to see.

This is what happened.

This is the coding I copied from

https://www.bluepecantraining.com/portfolio/excel-vba-macro-to-apply-conditional-formatting-based-on-value/

and then modified it.

Sub test()

    Dim rg As Range
    Dim cond1 As FormatCondition, cond2 As FormatCondition
    Set rg = Range("$C$4:$AG$14")

    'clear any existing conditional formatting
    rg.FormatConditions.Delete

    'define the rule for each conditional format
    Set cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, "weekday(C$5)=7")
    Set cond2 = rg.FormatConditions.Add(xlCellValue, xlEqual, "weekday(C$5)=1")


    'define the format applied for each conditional format
    With cond1
        rg.Interior.Color = vbYellow
        rg.Font.Color = vbBlack
    End With

    With cond2
        rg.Interior.Color = vbYellow
        rg.Font.Color = vbBlack
    End With

End Sub

Answer 1

1. Change the Type argument to xlExpression.
2. In your With statements, you should remove rg. What you are doing is applying the formatting onto the range (rg) itself, not on the FormatCondition object (cond1/cond2).

Sub test()

    Dim rg As Range
    Dim cond1 As FormatCondition, cond2 As FormatCondition
    Set rg = Range("$C$4:$AG$14")

    'clear any existing conditional formatting
    rg.FormatConditions.Delete

    'define the rule for each conditional format
    Set cond1 = rg.FormatConditions.Add(xlExpression, xlEqual, "=WEEKDAY(C$5)=7")
    Set cond2 = rg.FormatConditions.Add(xlExpression, xlEqual, "=WEEKDAY(C$5)=1")


    'define the format applied for each conditional format
    With cond1
        .Interior.Color = vbYellow
        .Font.Color = vbBlack
    End With

    With cond2
        .Interior.Color = vbYellow
        .Font.Color = vbBlack
    End With

End Sub