Conditional formatting via VBA

[handysmurf]

I need to convert these conditional formatting rules to VBA.

 

[Artik]

You can record a macro from the steps of defining conditional formatting. Since you already have the definitions, follow the following:
1. Select cell C8
2. Enable macro recording (in tab Developer)
3. On the Home tab, select Conditional Formatting/Rule Management, from the drop-down list select This sheet.
4. In the bottom list you have definitions, select the last one and choose the Edit Rule button (or double-click in the row), then do not change anything in the new window and immediately select the OK button.
5 Follow the same procedure as in point 4 with the next rules moving up the list.
6 Finally, close the Rule Manager window by pressing the OK button.
7. Finish recording the macro.
8. Go to the VBA editor , find the recorded macro and remove all occurrences ( if present) in the code:

Range("C8").Activate

Artik

 

[handysmurf]

Had not even thought of that.... I could also clear the formatting and then reset it ... all in the same one


Thanks!!!

 

[handysmurf]

uggghhhh .... it doesn't record what happens in the conditional formatting GUI

 

[Artik]

It looks like you need to buy a new recorder.

Below is an excerpt from the recorded conditional formatting procedure.

Sub Makro4()
'
' Makro4 Makro
'
    Cells.FormatConditions.Delete
    
    Range("C7:D25,F7:F25").Select
    Range("C8").Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND($C8<=90,$D8<=60)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("C8:D25,F8:F25").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OR($C8=""Meds"",$C8=""P.M.Meds"",$C8=""A.M.Meds"",$C8=""Wake"",$C8=""Sleep"")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).StopIfTrue = True
    Range("C8:D25,F8:F25").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ISBLANK($C8)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).StopIfTrue = True
    Range("A8:F25").Select
    Range("C8").Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A8<>$A9"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

And this is the revised code to optimize it a bit.

Sub Makro4_1()
    Cells.FormatConditions.Delete

    With Range("C7:D25001,F7:F25001")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
                              "=AND($C8<=90,$D8<=60)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority

        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 10092543
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With

    With Range("C8:D25001,F8:F25001")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
                              "=OR($C8=""Meds"",$C8=""P.M.Meds"",$C8=""A.M.Meds"",$C8=""Wake"",$C8=""Sleep"")"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).StopIfTrue = True
    End With

    With Range("C8:D25001,F8:F25001")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
                              "=ISBLANK($C8)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).StopIfTrue = True
    End With

    With Range("A8:F25001")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$A8<>$A9"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Borders(xlBottom)
            .LineStyle = xlContinuous
            .TintAndShade = 0
            .Weight = xlThin
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
End Sub

It may not matter much to you, but for users of non-English language versions, I remind you that when defining conditional formatting formulas with code, you should use the local format notation, not English.

Artik

 

[handysmurf]

Ok so I'm working with what you gave me ... trying to get it to work. One step at a time. I have the one that lines the days done and working. So ... I'm stuck at the colors. How do I put #F2CEEF into the VBA?

 

[handysmurf]

Ok ... baby steps ... I have the color using rgb but I'm working on making it lighter if anyone wants to share any hints.

 

[handysmurf]

Ok ... I got it ... had to build each step manually one by one ... but your code help a lot

THANKS!!

 

[handysmurf]

Also got it to copy and paste formulas automatically and do it all on save.... shrug ... anyway