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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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

New Member
Joined
Aug 28, 2013
Messages
1
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,141
Messages
5,442,631
Members
405,188
Latest member
maluenmaluen19

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top