Using VBA to apply conditional formatting to a range of cells

Question

I would like to know how to access the column in conditional formatting titled 'Applies To' and input my own conditions. I have included a screenshot for better reference.

My code for adding the syntax in conditional formatting is,

With Selection
  .FormatConditions.Delete
  .FormatConditions.Add Type:=xlExpression, Formula1:="=" & c.Address & "=TRUE"
  .
  .
  .
End With

I believe the code should be added in there but i just cannot find the correct syntax.

Update :

I updated my code to look like this,

With Range(Cells(c.Row, "B"), Cells(c.Row, "N"))
  .FormatConditions.Delete
  .FormatConditions.Add Type:=xlExpression, Formula1:="=" & c.Address
  .FormatConditions(1).Interior.ColorIndex = 15 'change for other color when ticked
End With

This would essentially make rows of a specific range relevant to where i placed the checkbox, have their background colour changed. The checkbox position is represented by c.Address where 'c' contains the location of the cell that i selected to place my checkbox.

Solution 1

You need to do something like this (Range("A25") is exactly what you are going to find):

With Range("A25")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, _
            Formula1:="=" & c.Address 
        '.
        '.
        '.
End With

and there is no need to write "=" & c.Address & "=TRUE", you can use just "=" & c.Address.

Solution 2

The "applies to" is inherent in the Selection that the With block is performed on.

Comments

[winhung]

Hi teylyn, what do you mean by inherent ? Do you mean there is a syntax that caters to the "Applies to" in Selection ?

[teylyn]

I mean that you have a range of cells selected. That is the range that will be used for the "Applies to".

[winhung]

Oh ok, i see. Yes that makes sense.

[Sergei Wallace]

How might this work for a function like IsBlank that requires an argument?