+ Reply to Thread
Results 1 to 6 of 6

Highlight Specific Cell based with condition

  1. #1
    Forum Contributor
    Join Date
    07-27-2017
    Location
    Dhaka
    MS-Off Ver
    MS Excel 2019/ 64 bit
    Posts
    159

    Highlight Specific Cell based with condition

    Hi Experts,
    In my whole work sheet I want to highlight any cell which is like that: any cell will be highlight if its next down cell contains same character. Example 1: A1=Pen, A2=Pen, A3=Pen, now A1 needs to highlight automatically & font will be bold also. Example 2: B1=Pen, B2=Paper, B3=Paper, B4=Pencil, B5=Pencil, B6=Paper. Now in this example I want to highlight automatically B2 & B4 as per condition. Hints: Next Down cell means next row cell & if next row cell is blank then condition will count next 2 row, as like if C1=Pen, C2=(Blank), C3=Pen then A1 will be highlight with Bold font. This condition can explain another way: if previous row cell character not match with next row cell then next row cell should highlight with bold font. Example 1: D1=Computer & D2=Laptop then Both will be highlight because both previous row cell value not match with next row cell value. Please help me.

    Regards
    rachelwahid
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Highlight Specific Cell based with condition

    I have removed the solved tag for you so that members are aware that you need help.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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

    Re: Highlight Specific Cell based with condition

    1. "any cell will be highlight if its next down cell contains same character."

    Do you mean contains the same character or do you mean equals the same cell? (Later on in your description you talk about cells being equal not characters). See point 6.

    2. e.g. A1="Pen" A2="Pink" Should A1 be highlighted?
    3. B1="Pen" B2="Pen" Should B1 be highlighted?
    4. A1="Pen" A2="Pen" A3="Pen" Should A2 be highlighted as well since the cell below it (B3) is also the same value?

    5. "Example 2: B1=Pen, B2=Paper, B3=Paper, B4=Pencil, B5=Pencil, B6=Paper. Now in this example I want to highlight automatically B2 & B4 as per condition"

    Highlight B6 as well presumably ?

    6. "Example 1: D1=Computer & D2=Laptop then Both will be highlight because both previous row cell value not match with next row cell value."

    This should really be Example 3.
    Now you're saying the cell value doesn't match - when you originally described this problem you said "contains same character" See my question in point 1 above.
    Why should any of these these be highlighted? This contradicts your statement of "any cell will be highlight if its next down cell contains same character" which we cant tell until you've specified clearly whether we are comparing entire cells or characters within cells.

    It looks like you are comparing entire cells not "a character" - You need to be specific, all this ambiguity leads to delays.
    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.

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

    Re: Highlight Specific Cell based with condition

    Is this any good as a solution?

    Select the entire range A1:C6

    Conditional Formatting
    New Rule
    Use a formula to determine...

    =COUNTIF(A$1:A$6,A1)>1

    Format as required

  5. #5
    Forum Contributor
    Join Date
    07-27-2017
    Location
    Dhaka
    MS-Off Ver
    MS Excel 2019/ 64 bit
    Posts
    159

    Re: Highlight Specific Cell based with condition

    Dear Special K, Thanks for your help but i need this another way. let me explain: e.g: A1=pen, B1=pen then A1 will be highlight bcoz A2 contain same character. e.g2: B1=pen, B2=chair, B3=chair, B4=table then B1 will be highlight bcoz B2 is different character, B2 will be highlight bcoz B3 is same character & B4 will be highlight bcoz B5 is blank. it will happen in my whole sheet.

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

    Re: Highlight Specific Cell based with condition

    Still not clear, you keep talking about characters
    Do you mean you are comparing complete values or individual single characters?
    A1="pen", The word "pen" is a value not a character. The letters "p" "e" and "n" are individual characters that together make up the word (or value) "pen".

    Another example

    A1="pen"
    A2="pound"

    A1 is NOT the same value as A2
    But both A1 and A2 begin with the same character (the letter "p")
    So are you comparing complete values or individual characters?

    Also you say highlight when A1 A2 contain the same character but in the B1 B2 example you're highlighting when they are different?
    So are we highlighting when things are the same or when things are different? If we are highlighting both are we using the same colour or different colours?
    Last edited by Special-K; 01-22-2019 at 12:56 PM.

+ 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] How to highlight a specific cell based on value/text/number
    By Byambadorj in forum Excel General
    Replies: 13
    Last Post: 07-18-2018, 03:41 AM
  2. Highlight specific cells with where condition
    By han02 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-01-2016, 12:09 PM
  3. Highlight specific cells with where condition
    By han02 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-01-2016, 08:14 AM
  4. Highlight pivot cells under a specific condition
    By Avyx in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-06-2015, 07:52 AM
  5. Replies: 1
    Last Post: 01-20-2014, 03:52 AM
  6. highlight specific cells in row based on one cell value in same row.
    By mgbanc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-22-2013, 06:58 AM
  7. Highlight the cell based on the specific condition
    By shaan65 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-25-2010, 02:07 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