Color background with Matching cells in row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I hace a worksheet that has many rows of data with 36 columns. I need to
find a way that if two or more columns in that row have matching values, I
can change the background color to red

Here is a example. The numbers are actually formatted as text and not numbers.
A B C D E F G
12 14 16 8 24 8 14 -----Cells B D F G should be red
15 5 6 5 6 2 9 Cells B C D E should be red

Any help would be greatly appreciated.

regards

Chris
 
Try something like this:
Assuming your data is in Cells A1:AJ100

Select that range, with A1 as the active cell
Format>Conditional formatting
Formula is: =COUNTIF($A1:$AJ1,A1)>1
Click the [Format...] button
Set the format you want, then click the [OK] buttons.

(Note the dollar signs in the forumula above)

Change range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Thank you ,but unfortunately it did not help. Is there any other way or
suggestions you may have?

Regards

Chris
Ron Coderre said:
Try something like this:
Assuming your data is in Cells A1:AJ100

Select that range, with A1 as the active cell
Format>Conditional formatting
Formula is: =COUNTIF($A1:$AJ1,A1)>1
Click the [Format...] button
Set the format you want, then click the [OK] buttons.

(Note the dollar signs in the forumula above)

Change range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


Diggsy said:
Hello,
I hace a worksheet that has many rows of data with 36 columns. I need to
find a way that if two or more columns in that row have matching values, I
can change the background color to red

Here is a example. The numbers are actually formatted as text and not numbers.
A B C D E F G
12 14 16 8 24 8 14 -----Cells B D F G should be red
15 5 6 5 6 2 9 Cells B C D E should be red

Any help would be greatly appreciated.

regards

Chris
Click to expand...
Click to expand...
 
try:
=COUNTIF($A1:$G1,A1)>1
--
Kevin Vaughn


Diggsy said:
Thank you ,but unfortunately it did not help. Is there any other way or
suggestions you may have?

Regards

Chris
Ron Coderre said:
Try something like this:
Assuming your data is in Cells A1:AJ100

Select that range, with A1 as the active cell
Format>Conditional formatting
Formula is: =COUNTIF($A1:$AJ1,A1)>1
Click the [Format...] button
Set the format you want, then click the [OK] buttons.

(Note the dollar signs in the forumula above)

Change range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


Diggsy said:
Hello,
I hace a worksheet that has many rows of data with 36 columns. I need to
find a way that if two or more columns in that row have matching values, I
can change the background color to red

Here is a example. The numbers are actually formatted as text and not numbers.
A B C D E F G
12 14 16 8 24 8 14 -----Cells B D F G should be red
15 5 6 5 6 2 9 Cells B C D E should be red

Any help would be greatly appreciated.

regards

Chris
Click to expand...
Click to expand...
Click to expand...
 
Actually, looking again at Ron's answer, the only difference in mine is that
he assumed range through AJ. I don't know why the answer he gave you
wouldn't have worked.
--
Kevin Vaughn


Diggsy said:
Thank you ,but unfortunately it did not help. Is there any other way or
suggestions you may have?

Regards

Chris
Ron Coderre said:
Try something like this:
Assuming your data is in Cells A1:AJ100

Select that range, with A1 as the active cell
Format>Conditional formatting
Formula is: =COUNTIF($A1:$AJ1,A1)>1
Click the [Format...] button
Set the format you want, then click the [OK] buttons.

(Note the dollar signs in the forumula above)

Change range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


Diggsy said:
Hello,
I hace a worksheet that has many rows of data with 36 columns. I need to
find a way that if two or more columns in that row have matching values, I
can change the background color to red

Here is a example. The numbers are actually formatted as text and not numbers.
A B C D E F G
12 14 16 8 24 8 14 -----Cells B D F G should be red
15 5 6 5 6 2 9 Cells B C D E should be red

Any help would be greatly appreciated.

regards

Chris
Click to expand...
Click to expand...
Click to expand...
 
Tried it again and it works great. Thanks Ron and Kevin.

Chris

Kevin Vaughn said:
Actually, looking again at Ron's answer, the only difference in mine is that
he assumed range through AJ. I don't know why the answer he gave you
wouldn't have worked.
--
Kevin Vaughn


Diggsy said:
Thank you ,but unfortunately it did not help. Is there any other way or
suggestions you may have?

Regards

Chris
Ron Coderre said:
Try something like this:
Assuming your data is in Cells A1:AJ100

Select that range, with A1 as the active cell
Format>Conditional formatting
Formula is: =COUNTIF($A1:$AJ1,A1)>1
Click the [Format...] button
Set the format you want, then click the [OK] buttons.

(Note the dollar signs in the forumula above)

Change range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


:

Hello,
I hace a worksheet that has many rows of data with 36 columns. I need to
find a way that if two or more columns in that row have matching values, I
can change the background color to red

Here is a example. The numbers are actually formatted as text and not numbers.
A B C D E F G
12 14 16 8 24 8 14 -----Cells B D F G should be red
15 5 6 5 6 2 9 Cells B C D E should be red

Any help would be greatly appreciated.

regards

Chris
Click to expand...
Click to expand...
Click to expand...
 
You're welcome....I'm glad that worked for you.


***********
Regards,
Ron

XL2002, WinXP-Pro


Diggsy said:
Tried it again and it works great. Thanks Ron and Kevin.

Chris

Kevin Vaughn said:
Actually, looking again at Ron's answer, the only difference in mine is that
he assumed range through AJ. I don't know why the answer he gave you
wouldn't have worked.
--
Kevin Vaughn


Diggsy said:
Thank you ,but unfortunately it did not help. Is there any other way or
suggestions you may have?

Regards

Chris
:

Try something like this:
Assuming your data is in Cells A1:AJ100

Select that range, with A1 as the active cell
Format>Conditional formatting
Formula is: =COUNTIF($A1:$AJ1,A1)>1
Click the [Format...] button
Set the format you want, then click the [OK] buttons.

(Note the dollar signs in the forumula above)

Change range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


:

Hello,
I hace a worksheet that has many rows of data with 36 columns. I need to
find a way that if two or more columns in that row have matching values, I
can change the background color to red

Here is a example. The numbers are actually formatted as text and not numbers.
A B C D E F G
12 14 16 8 24 8 14 -----Cells B D F G should be red
15 5 6 5 6 2 9 Cells B C D E should be red

Any help would be greatly appreciated.

regards

Chris
Click to expand...
Click to expand...
Click to expand...
 
Back
Top