+ Reply to Thread
Results 1 to 10 of 10

Conditional Formatting Question

  1. #1
    Forum Contributor
    Join Date
    06-04-2015
    Location
    Bangkok, Thailand
    MS-Off Ver
    2013
    Posts
    175

    Conditional Formatting Question

    Hi All,

    I tried to do conditional formatting by using a simple formula

    For example
    =A1=B2

    If this is true make it orange

    But for some reason I need to use $ for it to actually highlight

    =A1=$B$2

    I am attaching a sample sheet here

    How come I can't get it to highlight without the $ symbol?

    This make its hard to copy down as I need to go into conditional formatting to adjust the cell each time I do it.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,582

    Re: Conditional Formatting Question

    I think you sheet is full of redundant conditional formats and your example above does not correspond to the cells on the sheet. What do you want to achieve?
    perhaps select E5:E19
    conditional format using formula then =countif($b10:$b13,e5) >0 then format as you wish

  3. #3
    Forum Contributor
    Join Date
    06-04-2015
    Location
    Bangkok, Thailand
    MS-Off Ver
    2013
    Posts
    175

    Re: Conditional Formatting Question

    Hi Davsth,

    I tried your formula but nothing happened. Basically I want to highlight the dates in column E if they match the dates in column B.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,529

    Re: Conditional Formatting Question

    No, you don't. Excel will work it out for you. Your attachment doesn't match your request, however ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Forum Contributor
    Join Date
    06-04-2015
    Location
    Bangkok, Thailand
    MS-Off Ver
    2013
    Posts
    175

    Re: Conditional Formatting Question

    Hi AliGW,

    Sorry I'm a bit confused. I have 4 dates in column B (Apr May Aug Sep for year 2018) and then I have a list of dates in column E.

    How can I get the dates in column E to highlight if it matches the date in column B without using $.

  6. #6
    Forum Contributor
    Join Date
    06-04-2015
    Location
    Bangkok, Thailand
    MS-Off Ver
    2013
    Posts
    175

    Re: Conditional Formatting Question

    Let me try to clarify with a new file, apologies if I caused any confusion
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,582

    Re: Conditional Formatting Question

    for the conditional format to work for e5:e19 is has to remain a fixed range hence the $ signs. It is not reasonable to think that copying from E5 to E6 it remains fixed, but to move to rows 26 and below it doesn't and then does!

    My formula works, if you delete all the formula that you currently have in the conditional formatting, I did mention redundant conditional formats earlier, which still remain.

  8. #8
    Forum Contributor
    Join Date
    06-04-2015
    Location
    Bangkok, Thailand
    MS-Off Ver
    2013
    Posts
    175

    Re: Conditional Formatting Question

    Hi Davsth,

    I am attaching the sheet with your formula but for me it does not highlight.

    Can you provide an example of how it works? Or does anyone else have any clue as to how it works please?

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,529

    Re: Conditional Formatting Question

    Change the CF formula to this:

    =MATCH(E5,$B$10:$B$13,0)>0

    Do not change anything else. The applies to range is fine.

  10. #10
    Forum Contributor
    Join Date
    06-04-2015
    Location
    Bangkok, Thailand
    MS-Off Ver
    2013
    Posts
    175

    Re: Conditional Formatting Question

    Hi AliGW,

    Thank you very much. I have attached the excel where I tried your version but it still does not work once I copy and paste down the format.

    Please see attached. Is there a way around this without having to go in and modify the formula in conditional formatting?

+ 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 Question
    By snwbrdr19 in forum Excel General
    Replies: 11
    Last Post: 07-18-2017, 01:50 PM
  2. Conditional Formatting Question
    By jeff17408 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-08-2017, 12:59 PM
  3. [SOLVED] Conditional Formatting question
    By Avenger291 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-03-2013, 03:02 PM
  4. Conditional Formatting Question
    By PiedmontPrinting in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-15-2012, 11:41 AM
  5. Conditional formatting question
    By leanne2011 in forum Excel General
    Replies: 1
    Last Post: 09-16-2012, 06:27 PM
  6. Conditional Formatting Question
    By Dave_NI in forum Excel General
    Replies: 7
    Last Post: 04-22-2012, 06:38 PM
  7. conditional formatting question
    By fujimi-cho in forum Excel General
    Replies: 7
    Last Post: 10-22-2007, 03:29 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