+ 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,374

    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, 365
    Posts
    10,154

    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 NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    2,047

    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

    My internet connection can be intermittent, so sometimes I may not reply quickly to questions. Sorry - it's not personal and 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 NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    2,047

    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)

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