Conditional Format in VBA (part 2)

Question

Conditional Format in VBA (part 2)

Anonymous

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.

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 48,006

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

0 comments

6 additional answers

Answer 1

Anonymous

Updated: Blank Schedule

thank you,

K.

0 comments

Answer 2

OssieMac 48,006

I can't open RAR files. Please use the Windows Zip app.

0 comments

Answer 3

Good afternoon,

The value can appear anywhere in A38:A72 or can appear multiple times.

Afterthought on the CF / Stop True is correct. When the conditions are added, they are added/set in reverse order. The first condition that is entered is pushed to the bottom of the order and is the last condition to be applied. In this case, I would use first format sub, then use this format sub as this condition would be moved to the top of the list and be applied first.

When I run Private Sub ConditionalFormat() everything is working perfectly. When I run Sub FormatPlaceHolders(), the Run-time error '5': Invalid procedure call or argument is presented, with the Debug highlighting the Set Cond5 row.

Link to Workbook: Blank Schedule

thank you,

K.

Answer 4

Do you only have one cell (ie. A38) containing "***Lower Technician***"? If the "***Lower Technician***" can be in any cell in the range A38:A72 then NO $ sign prefix on the 38. That makes the column absolute but the row relative as follows.

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

If above does not fix the problem then please upload another example workbook with the ***Lower Technician*** at positions where it could be expected.

PS. An after thought. Because the CF is set up with "Stop is True" then it might need to be the first condition.