Answered Question: How To Keep Color Of Cells And Delete Conditional Formatting

Do not run the following code. It is left here for instructional purposes to demonstrate how it developed into a working macro. There are details in my next Code I posted that elaborate on what improvements were made and why for speed and functionality purposes.

Please Login or Register  to view this content.

you may try this code

Please Login or Register  to view this content.

"grid lines" can be referring to many things. There is a feature of Excel called the Macro Recorder. Start a macro recording, remove the grid lines to match your desired output, stop the recording, then you will have your code.

Yasser -- Thank you!

Matt,

I updated the following code so it doesn't take so long to run, it practically crashes Excel. I reduced the number of cells the macro loops over to exactly the cells that are used. I am still unable to display the gridlines and I'll update this again if I figure that out.

Anyone Viewing this thread: Do not run the following code. It is left below for instructional purposes. You can see the difference between this and the next code as to why this vs the other code takes so long to run. The addition of the parameter: LastColumnInRow and the loop over Rows instead of all cells in the range were key to speeding this up to practical runtimes with practical memory resources.

Please Login or Register  to view this content.

FINALLY!!!! Thanks to YasserKhalil and gbeats101, we got it! The following solution will not crash excel (I'm running 2015 excel w/ 8GB of RAM on a 64 bit operating system), completes the task when I loop over thousands of cells in less than 3s, and DOES NOT hide grid lines (they were there all along, but not visible because any interior color at all hides the gridlines and the .DisplayFormat.Interior.Color method seemed to be returning a color so slight that it was not detectable).

Your solution, sir:

Please Login or Register  to view this content.

Thank you, LJ. Not off-topic as well. Actually, I spent about two hours trying to figure that out.

Latest modification:

Please Login or Register  to view this content.

Hi Joe
I tried to comapre the code posted in post #6 and your last code in post #9 to find the difference but I couldn't find any difference
What is the new modification in Post #9?
Regards

Please note that the .DisplayFormat construction will only work in Excel 2010+.

There are other more involved solutions for prior Excel Versions such as:
http://www.get-digital-help.com/2013...ted-cells-vba/
http://www.mrexcel.com/forum/excel-q...our-cells.html
http://www.mrexcel.com/forum/excel-q...ormatting.html
http://www.excelfox.com/forum/f22/ge...ng-or-not-338/

In addition, I recommend against using numbers such as 1677215 in favor of either one of the built in Excel Constants: https://msdn.microsoft.com/en-us/lib...=vs.60%29.aspx
vbBlack
vbRed
vbGreen
vbYellow
vbBlue
vbMagenta
vbCyan
vbWhite

When using other colors my personal preference is to create pseudo-constants using RGB, since RGB can not be used in a constant. For example:

In an ordinary code module ModGlobals:

Please Login or Register  to view this content.

Here is a simple example that uses the code. Each Sub that uses a color should call the Initialize routine, otherwise there is a chance that the color Pseudo-Constant will Not be initialized and will have a value of 0 (ZERO) which is BLACK.

Please Login or Register  to view this content.

Lewis

Yasser,

The difference is very subtle. Aside from the recent edit to substitute vbWhite and 16777215, I used .Interior.ColorIndex = xlNone in post 9 instead of .Interior.Color = xlNone. That includes LJMetzger's contribution; although, .Interior.Color seems to work for Excel 2015.

LJMetzger,

Thanks again for going above and beyond with your contribution to this thread! You've inspired another question I'll try to figure out myself or post as a new topic.

Joe

Also will be better to only process cells with conditional formats - for this you may use specialcells.

Per https://msdn.microsoft.com/en-us/lib.../ff196157.aspx I see options:

XlCellType constants
Value
xlCellTypeAllFormatConditions. Cells of any format
-4172
xlCellTypeAllValidation. Cells having validation criteria
-4174
xlCellTypeBlanks. Empty cells
4
xlCellTypeComments. Cells containing notes
-4144
xlCellTypeConstants. Cells containing constants
2
xlCellTypeFormulas. Cells containing formulas
-4123
xlCellTypeLastCell. The last cell in the used range
11
xlCellTypeSameFormatConditions. Cells having the same format
-4173
xlCellTypeSameValidation. Cells having the same validation criteria
-4175
xlCellTypeVisible. All visible cells
12

...I'm not quite sure which would return conditional formats. Is this an incomplete list? If not, which wold you use, Izandol?

xlcelltypeallformatconditions is correct one.

Thanks!

For the curious reader as novice as I: http://www.mrexcel.com/forum/excel-q...onditions.html

LastColumnOfRow = R.Cells(R.Row, LastColumn + 1).End(xlToLeft).Column should have been used instead of
LastColumnOfRow = R.Cells(R.Row, LastColumn).End(xlToLeft).Column.

See below:

Please Login or Register  to view this content.

That should work and I'd like to know if it doesn't. I would expect LJMetzger's code (that I've adapted to your case below) works more quickly. LJMetzger took Izandol's idea of only looping through cells in the Used range that have conditional formatting and took care of the error that would occur if there is no conditional formatting on a worksheet. For that reason, the above code is not as efficient as the following.

Please Login or Register  to view this content.

*Corrected to include "With R/End With" in the above code at 5:53 PM (EST) on 1/11/2016
*Added an "If Not Rng Is Nothing/End If" in the above code at 6:01 PM (EST) on 1/11/2016 else there would probably be a runtime error if Rng is set to nothing due to a case where there is no conditional formatting in a worksheet.

I think you're right. http://answers.microsoft.com/en-us/o...1ad1d17?auth=1

The code will be updated w/I 5min.