+ Reply to Thread
Results 1 to 6 of 6

Conditional formatting with formula not working

  1. #1
    Registered User
    Join Date
    06-14-2019
    Location
    Michigan
    MS-Off Ver
    16.26, Excel
    Posts
    2

    Conditional formatting with formula not working

    I am trying to highlight all cells in a column that match the value in a specific cell. I select the header for column D, create a new rule in Conditional Formatting using the formula "=D4=$Q$3" to match all cells in the column against the current value in Q3. D4 is the first numeric cell in the D column. However, other cells are highlighted that don't match the value in Q3. Some are higher and some are lower. I've tried a number of different formulas that all give wrong results. Any help is appreciated.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Conditional formatting with formula not working

    I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,406

    Re: Conditional formatting with formula not working

    Hello dsteinhoff. Welcome to the forum.

    Make certain the data types agree --- ie that the numbers are actual numbers and not text that look like numbers. This is for both $Q$3 and the "Applies to:" range.
    Dave

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Conditional formatting with formula not working

    Welcome to the forum.

    Using D4 in the formula but applying it to the entire column will cause problems - you're formatting D1 based on D4, D2 based on D5, etc.
    Two alternative solutions:
    1. change D4 to D1 in the formula:
    =D1=$Q$3
    2. change the 'Applies to' from =$D:$D to $D$4:$D$1000 (or whatever range you need) - the formula will automatically change to =D7=$Q$3 so you'll need to change the D7 back to D4 as well.

    Hope that helps.
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  5. #5
    Registered User
    Join Date
    06-14-2019
    Location
    Michigan
    MS-Off Ver
    16.26, Excel
    Posts
    2

    Re: Conditional formatting with formula not working

    Thanks for the quick feedback. In creating a smaller example file that was suggested I realized
    that the highlighting was off by 3 rows, which coincidentally was the number of header
    text rows on the column. That was why I had put D4 in as the first value. I had the same problem
    when the range was $D:$D. This is what Aardigspook noticed and suggested. After I changed the range
    in the column from D:D to D4:D$, it now works as expected.

    Many thanks for the help.

  6. #6
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Conditional formatting with formula not working

    You're welcome, glad we could help.

    As you've only just joined, you may not know that you can mark the thread as Solved if your original question has been taken care of so others know there's an answer here. Instructions to do it are in my sig (see my post above) - if you could take a moment to do it, that would be appreciated. Thanks.

+ 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 formula not working
    By pipsmultan in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 04-05-2019, 09:54 AM
  2. And formula not working with conditional formatting
    By reasemorin in forum Excel General
    Replies: 7
    Last Post: 11-29-2018, 10:09 PM
  3. [SOLVED] Conditional Formatting formula not working
    By panama74 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-12-2018, 05:19 PM
  4. [SOLVED] Conditional Formatting Not Working With Formula
    By markd038 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-28-2014, 04:05 AM
  5. AND in Conditional Formatting Formula not working
    By readyemail in forum Excel General
    Replies: 11
    Last Post: 11-30-2010, 06:00 PM
  6. using a formula in conditional formatting not working
    By missmischa in forum Excel General
    Replies: 4
    Last Post: 02-22-2010, 06:04 PM
  7. Conditional Formatting with LARGE formula not working
    By peri1224 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-02-2010, 09:47 AM

Tags for this Thread

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