Conditional Format in VBA (part 2)

Anonymous
2020-09-17T05:32:29+00:00

Good afternoon, this is a continuation of "Cleanup Conditional Format in VBA" located at cleanup conditional format in vba.  A link to my workbook is provided in the original post, and I am using OssieMac's solution 1 as it is cleaner and smaller than solution 2.

As I continued to work on my schedule, I am able to manually create the following conditional format:

If cell A38=***Lower Technician*** then cells B38:O38 is black filled.  The conditional format is entered manually as follows:

Highlight B38:O38, New Rule, Use a formula..., Formula entered is =$A$38="***Lower Technician***", format is fill black and it works, and I would loop from rows 38 down to 72.

I want to keep this conditional format separate from what first solution, i am stuck at this point:

Dim PlaceHolder as Range

Dim Cond5 As FormatCondition, Cond6 As FormatCondition, Cond7 As FormatCondition, Cond8 As FormatCondition '(I will have four conditions to trigger the black fill)

Set PlaceHolder = Range("B38:O38")

Set Cond5 = PlaceHolder.FormatConditions.Add(Type:=xlExpression, Formula1:="=$A$38=***Lower Technician***")

I cannot apply the condition using vba.  I know that the issue is with entering the formula itself.  What should the formula be to capture ***Lower Technician*** as the formula when using vba.

Thank you,

K.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
OssieMac 47,631 Reputation points
2020-09-18T23:57:31+00:00

Last Question: Is there a variable that can be applied to the Cond5 through Cond8 that would clear the cell contents along with the .Interior.Color?  I see that there is a .Delete but but no ClearContents.

Conditional formatting cannot change the value of a cell. It can only change the Display Format.

However, the following code can be run separately to clear the contents based on the Display Format.

Sub ClearContentsBasedOnCF()

    Dim rngCF As Range

    Dim rCel As Range

    Set rngCF = Range("B38:O72")

    For Each rCel In rngCF

        If rCel.DisplayFormat.Interior.Color = vbBlack Then

            rCel.ClearContents              'Removes the value from the cell

            'rCel.FormatConditions.Delete    'Example: Removes ALL ConditionalFormat from the cell

        End If

    Next rCel

End Sub

Was this answer helpful?

0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-09-18T22:32:33+00:00

    Good afternoon,

    On combining the two subs into one sub - initial thought is yes, but no is the better answer.  The first sub is more complicated and takes me longer to follow through to see how it works.  The second sub is easier for me to follow along.

    Stop If True condition - I did see that the flag was being turned on when the formatting is applied with VBA.  After doing some reading I found that applying the code with VBA - the reverse become the default behavior.  When manually entering the formatting, you need to enable the Stop If True, when using VBA it's just turned on.  I adjusted the code by adding the StopIfTrue = False to the Cond# but the behavior was not what was expected.  But I was still in the right direction...

    On the Formatting itself - it does not work as intended but my fix worked as follows: I will run the codes in reverse order so that PlaceHolders is applied first, then the ConditionalFormat sub would become second.  With that, I will have to move the Rng.FormatConditions.Delete into the PlaceHolders sub.  When applying the subs in this order, the Stop If True condition is not a factor - tested and working but the text is visible and the row still adds up...

    Last Question: Is there a variable that can be applied to the Cond5 through Cond8 that would clear the cell contents along with the .Interior.Color?  I see that there is a .Delete but but no ClearContents.

    Otherwise, all of the formatting is completed (yay!)

    thank you,

    Was this answer helpful?

    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more