Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Conditional Formatting Troubles

  1. #1
    Board Regular giacomo's Avatar
    Join Date
    Feb 2002
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am using the following conditional format to bold certain cities that match to 4 specific cities:

    =MATCH(A1,CityList,0)

    where CityList is a named range that refers to cells $D$3:$D$6, the data contained in those cells are the following 4 city office names:

    Bakersfield
    Tucson
    SonomaMarin
    Austin

    This is working great with the exception of SonomaMarin, it bolds the other cities but not this one.

    I'm using excel 97 on windows NT.

    Thanks for your help,
    giacomo

  2. #2
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try checking for any extra spaces in your list and/or in A1

  3. #3
    Board Regular giacomo's Avatar
    Join Date
    Feb 2002
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-29 11:12, Brian from Maui wrote:
    Try checking for any extra spaces in your list and/or in A1
    Thanks for your reply, no additional spaces, I should have mentioned that in my post. I even checked the two using the EXACT formula and the result is TRUE.

    Also, when I use the MATCH formula in a cell, instead of within Conditional Formatting it gives the right result.

    [ This Message was edited by: giacomo on 2002-04-29 11:20 ]

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It works okay for me. Why don't you re-define your list? Maybe Sonoma\Marin didn't make the list properly.

  5. #5
    Board Regular giacomo's Avatar
    Join Date
    Feb 2002
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-29 11:22, bergy wrote:
    It works okay for me. Why don't you re-define your list? Maybe Sonoma\Marin didn't make the list properly.
    Thanks, I tried your suggestion but it didn't help. I even tried replacing the list with the cell reference instead but that didn't help either.

    Thanks,
    giacomo

  6. #6
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How's about

    =countif(range,a1)>=1

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Maybe it's an NT issue. I'm not up on NT, but could the '\' be causing the problem? Maybe NT thinks its a looking for a networked computer and not a string. Could you manually replace the '\' in CityList with a '-' and then do the same replacement in column A and see if it finds it then?

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-29 11:04, giacomo wrote:
    I am using the following conditional format to bold certain cities that match to 4 specific cities:

    =MATCH(A1,CityList,0)

    where CityList is a named range that refers to cells $D$3:$D$6, the data contained in those cells are the following 4 city office names:

    Bakersfield
    Tucson
    SonomaMarin
    Austin

    This is working great with the exception of SonomaMarin, it bolds the other cities but not this one.

    I'm using excel 97 on windows NT.

    Thanks for your help,
    giacomo

    Apply LEN to the target entry in the CityList:

    =LEN(D5)

    and do the same wrt to the entry in the range where you apply cond format.

    Are they equal?


  9. #9
    Board Regular giacomo's Avatar
    Join Date
    Feb 2002
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Brian from Maui & bergy,

    Thanks for sticking with me on this. I discovered the problem and it had nothing to do with my MATCH formula. I've been using conditional formatting to highlight every even numbered row in my worksheet, it just so happens that SonomaMarin was my only matching city that was on an even row.

    Once I removed the other conditional format it worked fine. Any ideas on how I can get the two to work together?

    thanks!
    giacomo

  10. #10
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin can!

    What order do you have the formatting?

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
  •