How to check whether the format of one cell is the same as another?

[superlative]

BACKGROUND: I have implemented pseudo-conditional formatting code, where a few conditions are checked and then if met, it will copy the format of a reference cell. The end effect is that conditional formatting appears to be in effect. The reason I don't want to just use conditional formatting, is because with conditional formatting, undo becomes very slow.

PROBLEM: when copying and pasting format on all cells of a worksheet, this can be slow. But you can avoid copying and pasting formats, if the format is exactly the same between the reference cell and the cell in question. Question: how to compare if the format between 2 cells is the same or not?

I know you can do:
if WS.Cells(row, col).interior.color = RWS.Cells(rrow, rcol).interior.color then...

but that only checks the interior.color, but doesn't check font.color, it doesn't check font style etc. Rather than do a whole bunch of individual if..then.. statements, is there a way to check if ALL formats between 2 cells are the same?

Thanks!

[mgs73]

I'm pretty sure the answer is no. Can you describe in more detail how undo becomes very slow with conditional formatting? Is it a particularly large workbook, lots of calculations, etc...?

[vshukla]

@superlative, Let me know if this helps you. first you need to decide that which all formatting you want to match as there are n number of formatting options available. You prepare a list of formats which you want to compare for cell and which will have impact on end result. Once you decide that you can use the "Display format property to compare the formatting of source and target cells. Below are few examples.

?activecell.DisplayFormat.Interior.Color
?activecell.DisplayFormat.Font.FontStyle
?activecell.DisplayFormat.Font.Color
?activecell.DisplayFormat.Style
?activecell.DisplayFormat.HorizontalAlignment
?activecell.DisplayFormat.WrapText
?activecell.DisplayFormat.Orientation

[superlative]

I'm pretty sure the answer is no. Can you describe in more detail how undo becomes very slow with conditional formatting? Is it a particularly large workbook, lots of calculations, etc...?

I have a worksheet where many cells had about 6 different conditional formats each. Undoing any action on them took over 5 minutes, and often just crashed the app - this is a known issue, look it up. I solved this by creating a template worksheet with formats for different cells, and then using VBA to determine which cell to copy format. This was as quick as just using conditional formatting, and made undo as quick as you would expect it to. The only time this is slow is if you want to refresh the format of the entire worksheet, and would therefore have to copy format on every cell in the worksheet - which is slow, and unnecessary if the format didn't need refreshing.

[superlative]

@superlative, Let me know if this helps you. first you need to decide that which all formatting you want to match as there are n number of formatting options available. You prepare a list of formats which you want to compare for cell and which will have impact on end result. Once you decide that you can use the "Display format property to compare the formatting of source and target cells. Below are few examples.

?activecell.DisplayFormat.Interior.Color
?activecell.DisplayFormat.Font.FontStyle
?activecell.DisplayFormat.Font.Color
?activecell.DisplayFormat.Style
?activecell.DisplayFormat.HorizontalAlignment
?activecell.DisplayFormat.WrapText
?activecell.DisplayFormat.Orientation

it helps me in that you've listed all of them, but it doesn't help me in that it emphasizes my problem. As mentioned in the post before this, I have a template worksheet, which allows users to modify the appearance of the main worksheet. I allow them to change whatever format they want.

I have a row, which is a "highlighted row" in the template. When the user filters rows, the row becomes highlighted in the main worksheet, because VBA will copy cells from this highlighted row in the template. However, the user is free to determine the format of this "highlighted row" in the template (eg. make the highlight green, or font bold, or font red).

Therefore when the user selects to highlight all rows, or un-highlight all rows in the main worksheet, this is slow because I am applying the copy/paste format on all rows - rather than JUST the rows that have incorrect format.

So in order to check if each cell has a different format than the reference cell, I would have to do if.. then statements on all 7 of those different formats on all cells. I was hoping there was a way to just check ONCE "if cell A has exactly the same format as cell B, then..."

[Pepe Le Mokko]

Administrative Note:

Please don't quote entire posts unnecessarily. They are just clutter and make threads hard to read.If you answer is out of sequence, perhaps add the name of the poster you are answering to
Use the "Quick reply" instead
Thanks

[mikerickson]

There is no "check all format" method in Excel VBA.

One work-around to speed things up is to designate one cell a the representative of a bunch of others.
For example, if you want to know if a range of cells has the same format as a KeyCell, you could check the top left cell of the range and act as if all the others in the range have the same format as that.

or

you could look down the first column of the Range and decide that a cell in that column is representative for the other cells in that row.

I'm curious about your reason for this. "because with conditional formatting, undo becomes very slow." If you are coloring cells with code, UnDo goes away (unless you write your own), so the pseudo-CF approach doesn't speed up the UnDo. I haven't noticed any time hit to UnDo with ConditionalFormatting.

[superlative]

Update: I created code to check every one of those 7 formats, it finished quicker than expected, so I'm happy with the performance. Here's the code:

Please Login or Register  to view this content.

[superlative]

There is no "check all format" method in Excel VBA.

...

FYI, I have code that controls cut, paste, drag and drop, insert, delete, clear contents. As far as I know, the only way to control some of these is to make use of undo. Eg. if you drag and drop something into a cell but want to swap a drag and drop overwrite with a drag and drop insert, then you have to detect the drag and drop overwrite, undo it, and then replace it with a drag and drop insert. Undoing a drag and drop over a bunch of cells that each have 6 conditional formats crashes the system.

It's a known issue - please look it up.
https://answers.microsoft.com/en-us/...9-7a3c38af5fcb

Good suggestion on a possible workaround. I was just thinking that I could also have a hidden column that contains the row # of the template worksheet that contains the format of the row of the main worksheet.