Conditionmal Format VBA-formula

  • I am trying to create multiple Conditional Format rules using VBA, but have been unable to work out the right syntax.


    Using Excel itself I get the correct result as follows:-

    > Home > Conditional Formating > Manage Rules > Edit Rule

    >>> Rule type ..... formula

    >>>Rule description ..... =SEARCH("ISA",$A10)

    >>> formats are set

    >>>Applies to ..... =$A$10:$Q$100


    What is the right way to convert the Rule-description-foumula into VBA?


    Thanks



    This is the best I have been able to come up with:-


  • I have a pivot table whose source data is itself of variable size, and needs to be refreshed frequently.

    This causes the pivot table to loose my 6 previous rules of conditional formatting, because the CF range sizes have changed.

    I was thinking that if I could write the CF rules by macro then I could auto renew them as needed.


    learning how to write CF rules by macro would help in many other workbooks & worksheets when my CF rules-ranges get jumbled up as I copy & paste.

    Many of my CF rules use formula, which work, but which I have been unable to convert to VBA.


    thanks for your time

  • Try recording a macro whilst entering the conditional formatting. This should give you the basic code with the formula. It will need editing afterwards.

  • Thank you for your reply,


    The first thing I originally tried was to record a macro, but it does not write any vba for :-

    selecting menu item=cf,

    entering the Formula

    selecting the formats

    etc.


    I just tried again, recording the macro with the VBA window open (ALT=F11)

    and could see it writing code for normal actions like ...Range("D45").Select

    but once I started selecting menu options, nothing got written to the new macro.


    that is why I tried finding code examples on the internet, but I couldn't work out the syntax.

    The code I found/worked out can work:-

    eg:

    Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=" & Selection.Cells(1).Address(False, False) & "=MIN(" & Selection.Address & ")")

    does work (copied direct from i/n)


    but my attempts (among many others) to write the formula I want, such as:

    Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=SEARCH("ISA",$A10)

    or

    Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=SEARCH(""ISA"",$A10)"

    get rejected

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!