Loop macro that removes conditional format conditions, leave static formatting.

Anonymous

I have the solution to remove conditional formatting, but leave format as is.

However, I have a large range to cover. I tried running AP through BB but, it took 20 minutes.

I broke it down to run one column at a time and it runs much smoother.

I need to: Make the macro loop through a range, instead of having to run each separately.

And if someone has an idea that works faster.... Please help.

Thanks

Sub CLEARCFS()

Application.Run "FormatrulesAP"

Application.Run "FormatrulesAQ"

Application.Run "FormatrulesAR"

Application.Run "FormatrulesAS"

Application.Run "FormatrulesAT"

Application.Run "FormatrulesAU"

Application.Run "FormatrulesAV"

Application.Run "FormatrulesAW"

Application.Run "FormatrulesAX"

Application.Run "FormatrulesAY"

Application.Run "FormatrulesAZ"

Application.Run "FormatrulesBA"

Application.Run "FormatrulesBB"

End Sub

Sub FormatrulesAP()

Dim ws As Worksheet

Dim mySel As Range, aCell As Range

'~~> Change this to the relevant sheet

Set ws = ThisWorkbook.Sheets("Synt")

'~~> Change this to the relevant range

Set mySel = ws.Range("ap4:Ap50000")

For Each aCell In mySel

With aCell

.Font.FontStyle = .DisplayFormat.Font.FontStyle

.Interior.Color = .DisplayFormat.Interior.Color

.Font.Strikethrough = .DisplayFormat.Font.Strikethrough

.Interior.Pattern = .DisplayFormat.Interior.Pattern

End With

Next aCell

mySel.FormatConditions.Delete

End Sub

Sub FormatrulesAQ()

Dim ws As Worksheet

Dim mySel As Range, aCell As Range

'~~> Change this to the relevant sheet

Set ws = ThisWorkbook.Sheets("Synt")

'~~> Change this to the relevant range

Set mySel = ws.Range("aQ4:AQ50000")

For Each aCell In mySel

With aCell

.Font.FontStyle = .DisplayFormat.Font.FontStyle

.Interior.Color = .DisplayFormat.Interior.Color

.Font.Strikethrough = .DisplayFormat.Font.Strikethrough

.Interior.Pattern = .DisplayFormat.Interior.Pattern

End With

Next aCell

mySel.FormatConditions.Delete

End Sub

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

11 answers

Anonymous

2019-03-04T19:09:14+00:00

Hi JW

Just before I create the macro let's clarify details.

1- According to the picture you posted.

Cells in Columns L, M, N, O... to.... AA =13 columns total they look for "mismatch" in columns

AC, AD, AE, AF .... to AO = 13 columns respectively.

2- All rows in columns start from row 4 to row 100000 , like you posted.

3- Column "P" with header "New/Used" has different value type and criteria, from the rest.

What to do with it??

4- Columns M, N and Q are hidden. Are they similar to Column P. What to do with it??

So please provide this info, in order to find solution.

Thanks
Was this answer helpful?

0 comments No comments
Anonymous

2019-03-04T18:21:42+00:00

Example: For Column L.

The conditional format is in L4 for column L.

It is =AC4="mismatch"

(The range is 100k rows because my data range changes...)

=$L$4:$L$100000

The fill is green if the matching row/column in AC is "mismatch".

Column O, P, R, S, T,... All look for "mismatch" in columns AD, AF, AG, etc....

Once the condition returns the color I need, I need to keep the color but get rid of the conditional format as 13 columns x 100 rows is a lot of active conditional formatting.

The example you gave removes the color if the rule is cleared but, I need the colors to stay.

Thanks
Was this answer helpful?

0 comments No comments
Anonymous

2019-03-04T17:30:00+00:00

Hi JW

I read carefully your reply, but i'm still think you do not need a VBA code to remove the conditional formatting in a range of cells or entire sheet, like I shown on my previous post.

Just select the columns you want to remove formatting and follow the steps shown in picture.

Regarding to formatting cells as static (colored cells remain colored without conditions attached).

To do that with VBA macro, you must provide us with the conditions for the cells and the colors you want to use in the formatting so we could create a VBA code to suit your purpose.

That's the way I think we should proceed.

Do let me know if you require any further help on this. Will be glad to help you.

Thanks

Regards

Jeovany CV
Was this answer helpful?

0 comments No comments
Anonymous

2019-03-04T15:40:14+00:00

Hi. The workbook that i am using is running very slow because I have 14 columns, and 50k+ rows of conditional formatting currently running, plus other formulas, and a load of data. I have reduced the workbook size and active formulas but, the conditional formatting is still causing it to run slow.

So, I am trying to apply the conditional formatting automatically using VBA, then remove the conditions and leave the formatting as static (colored cells remain colored without conditions attached). This will reduce the load on the workbook and things will run smoothly.

The formula above does this however, I am running each column one at a time (hence the application run... application run... application run...). I need to loop through the columns to speed up the process.
Was this answer helpful?

0 comments No comments
Anonymous

2019-03-01T19:02:03+00:00

Hi JW

I would like to help you but, unfortunately I don't quite understand your purpose,

Could you please post more information or details of your goal.

A screenshot of your data will help, Also post the results expected.

Remove any confidential / sensitive data.

However from your post, If you want to remove conditional formatting from a cell, or a range of cells

please do as follows in picture below.

Hope this will find a solution to your question.

If so, please mark it as answered

Do let me know if you require any further help on this. Will be glad to help you.

Thanks

Regards

Jeovany CV
Was this answer helpful?

0 comments No comments

Share via

Loop macro that removes conditional format conditions, leave static formatting.

11 answers