+ Reply to Thread
Results 1 to 10 of 10

Conditional formatting not working properly

  1. #1
    Registered User
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    Microsoft office 365 proplus
    Posts
    4

    Conditional formatting not working properly

    Hi,

    This may be a simple solution but I seem to be having some issues with conditional formatting. I am using Data Validation and INDIRECT forumula to reference a list for drop down. I have three INDIRECT references for drop down which works fine. When I switch the select a cell that is not refenced in the drop down the previously selected cell remains the same. Ive tried using a Macro to reset but considering there are multiple drop down on the sheet this might not be the right approach (not sure). So I am using conditional formatting to highlight the cells that do not reference the INDIRECT cells. It works for 90% of the cells that I select but there are a few that are remaining highlighed even though they are being reference properly. Drop downs are cells B2:B5. I have attached the file for more details.

    The spreadsheet has explanations on cells D2,D3,D5,D6.

    Can someone please help?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Conditional formatting not working properly

    In words what are you trying to do for these four cells B2 B3 B4 B5?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    Microsoft office 365 proplus
    Posts
    4

    Re: Conditional formatting not working properly

    Sorry for the confusion.
    I am trying to use conditional formatting to highlight the cells that does not match the selected drop down.

    Hope i am clearer this time.

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Conditional formatting not working properly

    Format looks complicated, dont have enough spare time for this, sorry.
    Hopefully someone will come along with a solution for you.

  5. #5
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Conditional formatting not working properly

    To solve the material validation issue, change your data validation rule to this for cell B4:
    Please Login or Register  to view this content.
    This forces MATCH to find an exact match.

    I'll try and look at the validation for Dyeing in a bit.
    Last edited by Melvosh; 03-12-2019 at 01:47 PM. Reason: Added explanation

  6. #6
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Conditional formatting not working properly

    The same type of change will work for the Dyeing validation rule:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    Microsoft office 365 proplus
    Posts
    4

    Re: Conditional formatting not working properly

    Quote Originally Posted by Melvosh View Post
    To solve the material validation issue, change your data validation rule to this for cell B4:
    Please Login or Register  to view this content.
    This forces MATCH to find an exact match.

    I'll try and look at the validation for Dyeing in a bit.
    Hi Melvosh,

    When I change the data validation formula for cell B4 to =ISERROR(VLOOKUP(B4,INDEX($A$17:$H$21,,MATCH(B3,$A$16:$H$16,0)),1,0)) i get an error.

    "The list source must be a delimited list, or a reference to single row or column:

    Can you attache the file with the updated formula.

    Also just double checking enter the formula in the data validation not conditional formatting?

    Thanks,

  8. #8
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Conditional formatting not working properly

    Quote Originally Posted by MindControl View Post
    Also just double checking enter the formula in the data validation not conditional formatting?
    You're absolutely right to ask that, because I definitely gave you bad direction. You should change the formula in the conditional formatting, not the data validation. Sorry about that!

  9. #9
    Registered User
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    Microsoft office 365 proplus
    Posts
    4

    Re: Conditional formatting not working properly

    Quote Originally Posted by MindControl View Post
    Hi Melvosh,

    When I change the data validation formula for cell B4 to =ISERROR(VLOOKUP(B4,INDEX($A$17:$H$21,,MATCH(B3,$A$16:$H$16,0)),1,0)) i get an error.

    "The list source must be a delimited list, or a reference to single row or column:

    Can you attache the file with the updated formula.

    Also just double checking enter the formula in the data validation not conditional formatting?

    Thanks,
    Hi Melvosh,

    I got it to work. I entered the formula into the conditional formating. Thanks you so much.

  10. #10
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Conditional formatting not working properly

    Happy to help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Conditional Formatting not working properly
    By Bmiha in forum Excel General
    Replies: 3
    Last Post: 07-10-2018, 04:45 AM
  2. Conditional cell formatting not working properly
    By luv2glyd in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-06-2017, 12:49 AM
  3. [SOLVED] Conditional formatting for icon sets is not working properly
    By Exceltrouble in forum Excel General
    Replies: 2
    Last Post: 09-16-2016, 08:43 AM
  4. [SOLVED] Conditional Formatting not working properly
    By Hodge1013 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-23-2015, 07:42 PM
  5. Conditional Formatting Formula Not Working Properly
    By Oscar Martin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-23-2014, 08:59 AM
  6. Conditional formatting not working properly
    By mlucey01 in forum Excel General
    Replies: 4
    Last Post: 01-08-2013, 08:35 PM
  7. [SOLVED] Conditional Formatting using VBA - Code not working properly
    By Tejas.T in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-07-2012, 10:19 AM

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