+ Reply to Thread
Results 1 to 13 of 13

Conditional Format: Highlight an Entire Column if value exists in another cell

  1. #1
    Registered User
    Join Date
    02-05-2013
    Location
    New York, US
    MS-Off Ver
    Excel 2010
    Posts
    43

    Conditional Format: Highlight an Entire Column if value exists in another cell

    I want to highlight an entire column using conditional formatting.

    If the text in Column A is contained within Column C, highlight that cell in column C. Otherwise leave that cell non-highlighted. I would like to apply this logic to an entire column.

    I have tried to select the range for the entire column within conditional formatting, but it came up with an error.

    Any insight on this particular issue would be great appreciated.
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Conditional Format: Highlight an Entire Column if value exists in another cell

    Hi,

    Using the Formula option in Cond. Formatting in C2 enter
    =IF(ISERROR(MATCH(A2&"*",$C$2:$C$13,FALSE)),0,1)=1
    and apply it to C2:C13 with your formatting colour/font etc.

  3. #3
    Registered User
    Join Date
    02-05-2013
    Location
    New York, US
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Conditional Format: Highlight an Entire Column if value exists in another cell

    Hello,

    Thanks for the assistance, I appreciate it. I have applied the formula and selected the range of cells, but I am only getting one of the cells to show up as not being highlighted. Cell C4 shows as not being highlighted, but the remaining cells become highlighted.
    Last edited by Netzqua; 03-02-2013 at 03:46 PM. Reason: add revision

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Conditional Format: Highlight an Entire Column if value exists in another cell

    Hi

    That's indeed what I thought you were wanting. i.e. if any of the values in column A are found in column C then highlight the cell in column C.
    The A4 cell with K1G is the only value that doesn't appear in column C, whereas all the other column A cells are to be found somewhere in column C.

    If that's not what you want (which is suggested by your original post), then manually format the cells you want to see highlighted, explaining with a note as to why and upload the workbook again.

    Regards

  5. #5
    Registered User
    Join Date
    02-05-2013
    Location
    New York, US
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Conditional Format: Highlight an Entire Column if value exists in another cell

    Oh, I see what you mean. My apologies for that I should have been more clear in my post, I have formatted the table the way that I would like to have it look and re-attached it.

    The effect I am after is.

    C4, C9 and C12 should not be highlighted because the value in A4, A9 and A12 does not appear in those cells. So looking at it row by row, if the value shows in the A, and it shows in C, then highlight it. if the value in A does not show in C, then leave C un-highlighted.Test.xlsx

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Conditional Format: Highlight an Entire Column if value exists in another cell

    Hi,

    OK, in that case

    =NOT(ISERROR(MATCH(A2&"*",C2,FALSE)))

    in C2 and applied to C2:C13

  7. #7
    Registered User
    Join Date
    02-05-2013
    Location
    New York, US
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Conditional Format: Highlight an Entire Column if value exists in another cell

    Thank You!!! Awesome!, I appreciate the help!

    Is it possible to now sort these rows so that the non-highlighted cells appear at the top?
    Last edited by Netzqua; 03-02-2013 at 04:32 PM.

  8. #8
    Registered User
    Join Date
    02-05-2013
    Location
    New York, US
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Conditional Format: Highlight an Entire Column if value exists in another cell

    never mind, i found it, this helps me a great deal, thanks Richard, you have save me hours of time

  9. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Conditional Format: Highlight an Entire Column if value exists in another cell

    Hi,

    OK, glad to have been able to help.

    Incidentally, when you have a moment may I suggest that you change your Excel version in your profile. When I saw your first post I was about to switch on Excel 2003 until I realised that you have at least Excel 2007. It didn't particularly matter in this case but it does often help when others are responding. Had your question involved more than 3 conditional formats (the limit in 2003) then you might have limited the responses you may have received.

  10. #10
    Registered User
    Join Date
    02-05-2013
    Location
    New York, US
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Conditional Format: Highlight an Entire Column if value exists in another cell

    okay, i have made the changes, my last question regarding this issue is,

    what if i wanted to match the criteria using column B instead of A?

    so example


    A
    K1C

    B
    HK1C005

    C
    K1C 5NY

    so instead of checking if A is in C,

    check to see if the bolded text in B exists in C

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Conditional Format: Highlight an Entire Column if value exists in another cell

    Hi,

    I don't know of a way for an Excel standard function to find and use bold characters from a string. You'd need a macro or user defined function for that. If by your example you mean it will always be the 2nd - 4th characters that you want to use then.

    NOT(ISERROR(MATCH(MID(B2,2,3)&"*",C2,FALSE)))

    I'd avoid the need to recognise cell or font characteristics, that's never straightforward. Try and use some standard function (like a string slicing function) or some other helper cell function to identify the subject of the match.

  12. #12
    Registered User
    Join Date
    02-05-2013
    Location
    New York, US
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Conditional Format: Highlight an Entire Column if value exists in another cell

    oh, okay, thats good to know, thank you so much for your help, i really appreciate it

    you must be an Excel MVP

  13. #13
    Registered User
    Join Date
    11-08-2013
    Location
    Jersey City, NJ
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Conditional Format: Highlight an Entire Column if value exists in another cell

    Hello Richard, I was trying to highlight only those values which are not found in the adjacent column modifying your formula (=IF(ISERROR(MATCH(C2&"*",$D$2:$D$499,FALSE)),0,1)=0 )and it worked pretty good. The question now is, what if you want to ignore blanks (NULL) ? For ex, say this what I have?


    8063470 8063470
    10245602 10245602
    11061324
    11064143 11064143
    12029101 12029101
    12997070 12997070
    13685658
    15606123 15606123
    16533407 16533407

+ 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