Results 1 to 3 of 3

Thread: xlCellTypeSameFormatConditions

  1. #1
    New Member
    Join Date
    Sep 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #2
    MrExcel MVP
    Join Date
    Jun 2002
    Location
    North Canton, OH USA
    Posts
    791
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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'"

    Jim

    Excel 2000; Windows 2000

  3. #3
    New Member
    Join Date
    Aug 2013
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: xlCellTypeSameFormatConditions

    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.

    Quote Originally Posted by Jim North View Post
    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •