Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Warsaw, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting (Excel, Office 2000)

    Need help in figuring out how to get Excel to recognize a word and sometimes some numbers for conditional formatting. I have a 52 sheet workbook and I want to apply the same conditional formatting to a group of cells that don't always have the same cell value.

    For example, all the cells have the word Group; but some of them have numbers (not all the same). For example some might have Group 1, another might have Group 2.5:, and another might only have the word Group.

    Any ideas how to get Excel to apply Conditional Formatting to these cells? Can it be done?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (Excel, Office 2000)

    I think the Search function is what you're looking for, combined with the Formula Is option in conditional formatting, although to be honest, I've never been able to get conditional formatting to work in Excel.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel, Office 2000)

    Charlotte is right, and there is a trick to getting conditional formatting to work. in this manner. Try this:
    1) Select a single cell to which you wish to apply the conditionoal formatting (in this example, A1)
    2) Choose Format (menu), Conditional Formatting...
    3) For Condition 1, choose "Formula Is", and type
    <pre>=NOT(ISERROR(SEARCH("Group",A1,1)))
    </pre>

    into the formula field. Also choose the formatting you wish.
    4) Click OK. Copy the cell (Ctrl-C or Edit, Copy)
    5) Select all the other cells you wish to format.
    6) Choose Edit (menu), Paste Special..., choose Formats and click OK.

    Why it works - in step 3, A1 is a relative reference. Therefore, when you paste special the format to the other cells, the reference will change according to the cell it is being applied to. Voila! If you select all the cell first, then create the condition, the format for every cell will depend on the value in one cell only, in this case, A1.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel, Office 2000)

    Jim,

    I would think you meant <pre>=NOT(ISERROR(SEARCH("Group",$A$1,1)))</pre>

    if all copied cells are to base the condition on the conrents of A1.

    or do I misynderstand you ?

    Andrew C

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel, Office 2000)

    If you type the formula manually (using relative reference), you can use either technique (select all cells first and apply conditonal format, or apply to one cell and copy formats to the other cells). However, if you build the formula by clicking on the cells to be tested, Excel automatically uses absolute reference, in which case, you should remove the $'s.

    PS - I'm using Excel 2000 right now, and both techniques worked. However, I am suspicious that Excel 97 did not use the proper relative references if you selected all the cells first (otherwise, I would not have gotten into the habit of using the apply-to-one-cell-then-paste-formats technique). I could be wrong, so I will try it when I get home.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel, Office 2000)

    I would use:

    <pre>=UPPER(LEFT(TRIM($A$1),5))="GROUP"
    </pre>

    Legare Coleman

  7. #7
    Lounger
    Join Date
    Jan 2001
    Location
    Warsaw, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel, Office 2000)

    Thanks for all the help. I really apprecaite you all taking the time to help me in crisis. Damon Longworth from another list also suggested the following:

    Cell value is between ="group " and ="group 99"

    This also works. Thanks again!!

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    Warsaw, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel, Office 2000)

    I tried this =NOT(ISERROR(SEARCH("Group",A1,1))) and it works without having to do a Paste Special. I just selected a range of cells then proceeded to do conditional formatting. The formatting was applied to only the cells with the Group ?, or Group ?.? or Group ?.??:

    Thanks!!

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel, Office 2000)

    Cool! The formatting will be applied to any cell with the string "Group" anywhere in it. In wildcard notation, any cell like this:

    *Group*

Posting Permissions

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