|

# » Coloring One Record in Each Group in a Range

To color one record in each group in a range, use Conditional Formatting:

1. Select the range of cells (or select the entire column).
2. From the Format menu, select Conditional Formatting.
3. In the Condition 1 box, select Formula Is.
4. Enter the following formula (make sure you are careful about the Absolute and Relative references): =COUNTIF(\$A\$2:A2,A2)=1
5. Click Format, and select the Patterns tab.
6. Choose any color, and then click OK twice.
Screenshot // Coloring One Record in Each Group in a Range

Rate This Tip
 1 2 3 4 5
Rating: 3.19     Views: 35942
colouring uniqu record
Ashu  Posted on: 31-12-1969
Only the perticular cell is coloured , how can I colour the enrire row of a unique record.
re: coloring unique record
Maria   Posted on: 31-12-1969
If you have more than one column of data, then in Step 2, first press CTRL+SHIFT+RIGHT ARROW to select all the columns of data, and then press CTRL+SHIFT+DOWN ARROW to select all the rows of data. Then continue with the remaining steps.
If you have more than one column
Helmut Kundel  Posted on: 31-12-1969
And in step 5:
=COUNTIF(\$A\$2:\$A2,\$A2)=1
Set the colour of data through formula
Arvind  Posted on: 31-12-1969
I want a function through which I can set or change the format of a cell entry. Although I can do it through "conditional formatting" option, but it permits only three conditions. I want more options.
coloring cells
Neeraj Chowdhury  Posted on: 31-12-1969
If I want that the entire row to be colored whenever a cell has a certain value, say if cell has the value "A", then the entire row should be colored red, and so on. How can I achieve this task.
Counting cells only when colored is applied.
Prasad Rao S  Posted on: 31-12-1969
Hi,
can anyone help me in this. I have a range of cells with numbers. I use count function to count the range. But I want a cell to be counted or added to count function ONLY when I FILL COLOR to a particular cell. is this possible?
Coloring Cells
Ralph Hopper  Posted on: 31-12-1969
"If I want that the entire row to be colored whenever a cell has a certain value, say if cell has the value "A", then the entire row should be colored red, and so on. How can I achieve this task."

I am also searching for this answer. Any help for this novice would be appreciated...
Identify only first value in a range that meets criteria
Seth  Posted on: 31-12-1969
I would like to write a formula that identifies only the first cell in a range of values that is greater than zero.
Reply: Identify only first value in a range that meets criteria - Seth from Aspen, CO wrote on August 18, 2003 2:30 PM EST
Alan  Posted on: 31-12-1969
Hi Seth,

Try this:

{=INDEX(Range,MATCH(TRUE,(Range)>0,0))}

This assumes, at least implicitly, that 'Range' is one-dimensional.

This is an array formula, so enter it without the curly brackets, using Shift-Ctrl-Enter.

Alan.
Troubleshooting nested (if(and( formula
Seth  Posted on: 31-12-1969
I am using a nested (if(and( formula that is giving me a value of "false". However, the conditions are clearly met by the last if/and statement and not met by any of the previous if/and statements of which there are five in total. What could be the source of this problem?

Thank you very much for your help.

Seth
Reply: Troubleshooting nested (if(and( formula - Seth from Aspen, co wrote on August 20, 2003 5:06 PM EST
Alan  Posted on: 31-12-1969
Hi Seth,

Probably the reason is that things are not as 'obvious' as they may appear.

I suggest you take your nested IFs and pull them out into separate cells (take them to a calculation sheet if necessary).

It is generally bad form to nest IF statements to deep anyway, since they become very difficult to 'read' and de-bug (as you have discovered).

Once you have done that, you will find that one specific logical test is not behaving as you expect. If you still can't work it out, post that specific test / data back here, and hopefully we can help.

Alan.
Color Based on Key Words
David Morrison  Posted on: 31-12-1969
Hello,

Lets say I have a set of numbers in cel A1 and I have a description in cell G1. If I type the words test in G1, I want A1 to become a color. How would I do this?

Thank you
Reply: Color Based on Key Words - David Morrison from Chicago IL wrote on August 26, 2003 3:42 PM EST
Alan  Posted on: 31-12-1969
Hi David,

Just use the conditional formatting feature (select the 'FORMULA IS' option).

The help facility explains it in detail quite neatly.

Alan.
Conditional Formatting
David  Posted on: 31-12-1969
Was trying to find a way to colour cells automatically; never even knew that this function existed- was lucky to have found this site on the web.

Just what I needed

Many thanks

David
Name
Comment Title