xlCellTypeSameFormatConditions

Dragonfly

New Member
Joined
Sep 12, 2002
Messages
2
Can anyone give me an example of how this constant works with the SpecialCells method? I can never get it to return anything.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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'"
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top