# Formatting repeating cells

1. ## 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.

2. ## 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. ## Re: Formatting repeating cells

Hi,

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. ## Re: Formatting repeating cells

Look attachment. Is this what you want?

Book2.xls

5. ## 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.

6. ## Re: Formatting repeating cells

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

Attachment 61478

Yes, this is what I exactly want.

"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. ## 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. ## Re: Formatting repeating cells

Originally Posted by zbor
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?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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