xlCellTypeSameFormatConditions

[Dragonfly]

Can anyone give me an example of how this constant works with the SpecialCells method? I can never get it to return anything.

 

[Jim North]

Found this on another board:

"Hi Denis,

I must admit... the SpecialCells method can be a little
bit tricky. I have prepared a summary below explaining
what each of the types are - and when they will work.

I hope this helps!
Cartman




Summary

xlCellTypeAllFormatConditions - This function will "work"
if the specified range contains any conditional
formatting. Any cells containing conditional formatting
will be selected out of the specified range. If no
coditional formatting is implemented, you will get a run
time error '1004'.

xlCellTypeAllValidation - This will only work if there is
data valadation in place in the specified range. Any
cells containing data validation will be selected form the
specified range. If no data validation is implemented,
you will get a run time error '1004'.

xlCellTypeBlanks - This will only work if a blank cell
exists in the specified range... It will select any blank
cells in the specified region. If no data blank cells are
within the specified range, you will get a run time
error '1004'.

xlCellTypeComments - THis will only work if a comment is
attached to a cell within the specified range. All cells
containing comments will be selected. If no cells contain
a comment, you will get a run time error '1004'.

xlCellTypeConstants - this will only work if a cell
contains a constant in the specified range. All cells
containing constants will be selected. If no cells in the
specified range contain a constant, you will get a run
time error '1004'.

xlCellTypeFormulas - this will only work if a cell within
the specified range contains a formula. All cells
containing a formula will be selected. If no cells in the
specified range contain a formula, you will get a run time
error '1004'.

xlCellTypeLastCell - this will select the last cell that
was used in the specified range. This is somewhat
decieving... suppose you had once put something into cell
ZZ500. But now, your current "range" only goes out to
C4. If you called Worksheets(XXXX).Cells.SpecialCells
(xlCellTypeLastCell).select - it would select ZZ500!

xlCellTypeSameFormatConditions - this will select all
cells in the specified range that contain the same format
as the *first* cell specified in range. If the first cell
specified in the range does not have conditional
formatting - you will get a run time error '1004'.

xlCellTypeSameValidation - this will select all cells in
the specified range that have the same validation scheme
applied as the *first* cell in the range. If the first
cell in the range does not contain data validation, you
will get a run time error '1004'.

xlCellTypeVisible - this will select all the visible cells
in the specified range. If all the cells in that
specified range are not visible, you will get a run time
error '1004'"

 

[EasternSierra]

I know this thread is super old, but I came accross it when I was researching how to use xlCellTypeSameFormatConditions constant. It appears that the quote below is not entirely accurate.

xlCellTypeSameFormatConditions - this will select all
cells in the specified range that contain the same format
as the *first* cell specified in range. If the first cell
specified in the range does not have conditional
formatting - you will get a run time error '1004'.

For instance, it should say "this will select all cells in the worksheet that contain the same format as the 'first' cell in the specified range".

Also, one should point out that this method and argument only applies to conditional formatting that is grouped under the same rule in the "Conditonal Formatting Rules Manager" dialog box. For instance, if you go to Home->Styles->Conditional Formatting->Manage Rules->Show formatting rules for: This Worksheet then it may show multiple rules with the same conditional formatting. This SpecialCells(xlCellTypeSameFormatConditions) code will only return the cells that are listed in the same line in that dialog box. So for instance if you copy some conditional formatting to another cell in your worksheet then that conditional formatting rule will be listed on a separate line in the "Conditional Formatting Rules Manager" dialog box and those cells will not get picked up when you used this method to identify cells with the same conditional formatting. Seems like a bug that Microsoft should maybe fix.