+ Reply to Thread
Results 1 to 5 of 5

Conditional formatting not working properly

  1. #1
    Registered User
    Join Date
    01-02-2013
    Location
    Reading, PA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Conditional formatting not working properly

    Hello All, I have a conditional format for a cell that I can get to work if I split it, but when I use the or statement in one condition the very simple portion of the condition does not change the cell formatting.

    =OR(AG21=$AG$1,AND(LEFT(AG21,1) = LEFT($AG$1,1),SEARCH("E",AG21)))

    To break it down a little further, the first condition AG21=$AG$1 will not change the color of the cell when this condition is true while I am using it as part of the OR statement. However, alone it works fine.

    Alone or in the OR statement the second condition always works AND(LEFT(AG21,1) = LEFT($AG$1,1),SEARCH("E",AG21)).

    Any help would be greatly appreciated.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,700

    Re: Conditional formatting not working properly

    Hi mlucy and welcome to the forum

    Its a little hard to make out what you are trying to do without seeing the data you are working with.

    i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-02-2013
    Location
    Reading, PA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Conditional formatting not working properly

    Sorry about that, I am new to posting a questions. I usually just read through the threads and can find what I need, so I should have known better any way. The file is attached.

    I have a yearly calendar and next to the weeks I have three different schedules for the three different shift rotations we have at the facility. Next to that I have a list of the operators and supervisors with there assigned shifts. Above the employee list is a drop-down box where a user can select an employee. When the employee is selected the cell next to the selection changes to show the shift name that is used as a reference to determine what their particular shift rotation is.

    Example: Operator 1 and Operator 11 are on shift A and their work schedule rotates the same. Operator 21 and Operator 23 are on shift C-FWD, they rotate on a different schedule than Oparator 1 and Operator 11.

    Along with the Operators there is a Supervisor that is repsonsible for certain Operators. For example Supervisor 1 is assigned a shift name of A-12WE, which is a slightly different shift rotation from shift A and A-FWD, but the supervisor is still responsible for the Operators of those shifts.

    So when an employee is selected AF1 and their shift name is shown in AG1 I have conditional formatting that highlights the particular shift name in the appropriate schedule rotation. This work fine by the way. I was also able to also change the color of the cells in the employee list that would also work with that employee on the exact same shift. This is done using the shift name in AG1 and looking for matches in AG9:AG36. I have been able to change the selected employees cell to blue and the other employees working with them to yellow. The problem I am having is trying change the color of the cell for the supervisor that is reponsible for the employees on that shift even when the shift name does not match exactly. I can do all of this using three separate conditional formats, but I am trying to combine changing the cell of the employees that work with the selected employee and the supervisor of that employee to yellow all in one condition. The problem is when I try to combine the two separate working conditions with the OR statement my simple condtion that checks for an exact match of the selected employees shift with anything in AG9:AG36 is ignored, but the more complicated portion of the condition that is trying to determine if which supervisor is responsible for the shift colors the cell when it should and does not when it should not. For some reason when I put the two working conditions together with the OR statement this very simple condition AG9=AG1 fails to be recognized.

    I hope I provided enough clear information for you to look at it. Thanks for your help.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-02-2013
    Location
    Reading, PA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Conditional formatting not working properly

    By the way, please ignore the third condition in the cells in column AF. this is actually my left over condition to check the more complicated portion of the second condition.

    Hopefully this does not confuse the matter for anyone.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,700

    Re: Conditional formatting not working properly

    the formula for CF must return either a TRUE or FALSE. (I often create my CF formulas IN the worksheet top test them, before I put them inside the CF function)

    part of your formula contains a search() function, which, if it cant find what it's looking for, returns an error. I changed that part to reflect a find or not...

    =OR(AG9=$AG$1,AND(LEFT(AG9,1) = LEFT($AG$1,1),ISNUMBER(SEARCH("E",AG9))))

    you need to do the same for the 3 CF condition. I cant really tell if that is now doing what you want it to, but Im sure that you will be able to. let me know how you make out?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1