Highlighting Entire Row

Excelprobs

New Member
Hello Guys,Gals.

For sometime now i've been searching how i could highlight entire rows in excel based on the data in one column. like for example
----A--- B--- C--- D--- E
1---a---as---we---fg---sd
2---a---fv---fd-----ij---ok
3---b---kh---hj----pl---uh
4---b---er---rt-----ty---ty
5---c---kj---kl-----as---oe
6---d---er---cv----cv---we

i made a formula that highlights the last occurance of the duplicates in a column. therefore A2,A4,A5 and A6 will be highlighted. A5 and A6 are highlighted even though it has only one occurance which is exactly what im looking for the conditional formatting to do.

the folmula i used in conditional formatting is

=IF(COUNTIF(\$B\$2:\$B\$11,B2)=1,TRUE,COUNTIF(\$B\$2:B2,B2)=COUNTIF(\$B\$2:\$B\$11,B2))

Now iam limited to highlighting only one column. Can anyone help mewith highlighting the entire row of the cells that are already highlighted?

Thanx for all the help in advance

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Welcome to MrExcel Board...

is that formula working for you? You don't usually need an if for conditional formatting as this is a true/false feature.

To highlight the entire row you would want to select all the data area then add your conditional format.

Hello texasaylnn

Yeah the formula is working perfectly for me. I have tried what u suggested already many times, but it isnt highlighting the entire row. it does highlight the last occurance of the duplicates in that column though. The formula is not my own but i do understand it. I found the formula that i used on this helpful website http://www.cpearson.com/excel/Duplicates.aspx

ok so if you highlight all of your data then make the formula be
Code:
=IF(COUNTIF(\$B\$2:\$B\$11,\$B2)=1,TRUE,
COUNTIF(\$B\$2:\$B2,\$B2)=COUNTIF(\$B\$2:\$B\$11,\$B2))

texasalynn,

thank you! i dunno y it didnt work before when i selected everything and highlighted it but for some reason its working now.

Hey guys,

can you help me with your knowledge?

I have a sheet in size of field B5:AF31.

PROBLEMS:

in row B5:AF5--> if in fields from B5:AF31 is one (or more) written number that is less than or equal 30--> row has to be all white

in the same row B5:AF31 --> if in fields from B5:AF31 is one (or more) written number that is greater than 30--> row has to be all gray

I have to make this for entire sheet but if I make this for more row, I can copy to entire sheet, am I right?

At the end of sheet B5:AF31, in cell B35: there should be a formula that counts all ROWS that are coloured white

in cell B36: there should be a formula that counts all ROWS that are coloured gray.

Is this possible?

Every INFO is welcomme

Replies
7
Views
211
Replies
3
Views
183
Replies
0
Views
203
Replies
1
Views
672
Replies
9
Views
316

1,203,073
Messages
6,053,379
Members
444,660
Latest member
Mingalsbe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back