Macro Which Highlights Rows That Contain Identical First Cells

L

luu980

Does anyone know of or can write a macro which will highlight the entire
row of cells in which the values in A column are identical / repeats.

What I need is this, for example:

aaaa bbbb bbbb bbbb
aaaa dddd dddd dddd
aaaa cccc cccc cccc
1111 1111 1111 1111
mmmm xxxx xxxx xxxx
mmmm tttt tttt tttt
9999 9999 9999 9999
yyyy hhhh hhhh hhhh
yyyy iiii iiii iiii
0000 0000 0000 0000
ssss mnjk zzzz dddd
ssss fdfc ftgy edsw

Leaving me:

Thus the rows which contain 'aaaa', 'mmmm', 'yyyy', 'ssss' will be
highlighted because the values in Column A are identical to each other.


(Note, the highlight can all be one color, even though, for example:
'aaaa' and 'mmmm' are not identical values.


I don't know how to write macros at all and wasn't able to find one
that does this. I am eternally grateful or any help that can be
provided.
 
T

Tom Ogilvy

Select cell A2. then click on the row 2 label on the left of the worksheet
(to the left of column 1). Then hold down the shift key and go to the last
row you want to have this behavior and click on that row label. Assume that
is row 100. Now row 2:100 are selected Make sure A2is the ActiveCell.

Now go to format=>conditional formatting

In the dialog, change the "Cell Value Is" dropdown to "formula is"
in the formula box put in
=AND($A2=$A3,$A2<>"")
then click formatting and select the color from the pattern tab.

Now click Add
Again, change to "Formula Is" and put in a formula like

=AND($A2=$A1,$A2<>"")

click the format button and select your color from the pattern tab.

no OK you way out.

If row1 is to be include as well, then select row1 alone with A1 as the
active cell and put in a formula like

=AND($A1=$A2,$A1<>"")

with appropriate formatting
 
L

luu980

Hi Tom,

I just tried out your solution. Works like a charm!!! Thank you very
much!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top