+ Reply to Thread
Results 1 to 8 of 8

Formatting repeating cells

  1. #1
    Registered User
    Join Date
    01-01-2010
    Location
    Manila
    MS-Off Ver
    Excel 2007
    Posts
    9

    Formatting repeating cells

    Hello everyone.

    I'm new here on this forums. Normally when I don't know how to do certain stuff, I just search Google. But this time, I think it needs a larger explanation for my problem.

    So let's say I have this column that I constantly fill in with data (This is Column A):

    apple
    banana
    orange
    apple
    grapes
    kiwi

    Now, my next entry is apple. Now I want all the cells (and some cells on their rows as well) to have a change in fill color or something whenever an entry appears THRICE. Something that will highlight it.

    In programming terms, this will mean that everytime I enter another value for Column A, it will have to crosscheck all the values of the entire Column A and check if my current entry appears three times or more. Then it uses some sort of conditional formatting (?) to highlight all entries with "apple" in Column A.

    I don't know how to do this. Please help.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,575

    Re: Formatting repeating cells

    try this in conditional formatting (use formula to determine wich cells to format):

    =COUNTIF($A$1:$A$1000,A1)>2

    and format in color you like

  3. #3
    Registered User
    Join Date
    01-01-2010
    Location
    Manila
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Formatting repeating cells

    Hi,

    Thanks for the quick reply.

    Excel 2007 explained to me how COUNTIF works, and apparently, the 2nd parameter is the criteria.

    I see in your statement that your criteria is A1. So that means every row will just cross reference to the "criteria" that compares all cells to A1?

    What I wanted to do was the make the current cell (the one that is being edited/inputted with value) the criteria.

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,575

    Re: Formatting repeating cells

    Look attachment. Is this what you want?

    Book2.xls

  5. #5
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Formatting repeating cells

    See this link for help and examples on understanding Conditional Formatting

    Tip: when using formulas for Conditional Formatting, the formula must evaluate to either true or false.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  6. #6
    Registered User
    Join Date
    01-01-2010
    Location
    Manila
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Formatting repeating cells

    Quote Originally Posted by zbor View Post
    Look attachment. Is this what you want?

    Attachment 61478

    Yes, this is what I exactly want.

    I saw your conditional formatting.

    "COUNTIF($A$1:$A$1000,A1)>2"

    and tried it on Column B, but I don't get the exact results. I don't get any result at all. How do you exactly do that? My method is highlight entire column then conditional formatting.

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,575

    Re: Formatting repeating cells

    Make coditional formating for first cell.

    Make sure you lock range and don't lock cell:

    COUNTIF($B$1:$B$1000,B1)>2 (no $ around B1)

    Then select cell -> copy -> select range -> Paste as spetial -> Formats

  8. #8
    Registered User
    Join Date
    01-01-2010
    Location
    Manila
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Formatting repeating cells

    Quote Originally Posted by zbor View Post
    Make coditional formating for first cell.

    Make sure you lock range and don't lock cell:

    COUNTIF($B$1:$B$1000,B1)>2 (no $ around B1)

    Then select cell -> copy -> select range -> Paste as spetial -> Formats

    It still doesn't work.

    What do you mean by
    "Make sure you lock range and don't lock cell:"


    Here is exactly what I did:

    1. Select A1. Conditional format it with Red Fill, with the following line: COUNTIF(A$1:A$1000,A1)>2 under the "Use the formula to determine which cells to format".
    2. Right click on A1 and select "Copy..".
    3. Click on the entire column A by click "A" above, then right click>Paste Special>Formats.

    I can't seem to do it right. And can someone explain to me why the "criteria" field is A1?

+ 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