how do I copy repeated cells

[petethecat]

What I want to do is identify columns/cells that have repeated/duplicated data in (for example) row1.
When I enter data in row2 in a cell/column that has been indentified as repeated/duplicated data then the same data is automatically copied across EACH cell that has been identified as repeated/duplicated.

<HR style="BACKGROUND-COLOR: #ffffff; COLOR: #ffffff" SIZE=1> <!-- / icon and title --><!-- message -->
<TABLE style="WIDTH: 440pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=588><COLGROUP><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 967" width=34><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 3214" width=113><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 1792" span=7 width=63><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; WIDTH: 26pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=34></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; WIDTH: 85pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=113>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; WIDTH: 47pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=63>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; WIDTH: 47pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=63>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; WIDTH: 47pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=63>D</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; WIDTH: 47pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=63>E</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; WIDTH: 47pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=63>F</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; WIDTH: 47pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=63>G</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; WIDTH: 47pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=63>H</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP-COLOR: windowtext; HEIGHT: 15pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>MODULE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>5100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>5101</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>5102</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>5103</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>5100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>5102</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>5102</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP-COLOR: windowtext; HEIGHT: 15pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>CANDIDATE 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: red; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: red; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP-COLOR: windowtext; HEIGHT: 15pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>CANDIDATE 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP-COLOR: windowtext; HEIGHT: 15pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>CANDIDATE 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP-COLOR: windowtext; HEIGHT: 15pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>CANDIDATE 4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP-COLOR: windowtext; HEIGHT: 15pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>CANDIDATE 5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD></TR></TBODY></TABLE>

in this example cell B1 & F1 are the same. Also cells/columns D1 G1 & H1 are the same. So when I enter a digit (in this case a "3") into B2 then F2 is automatically updated with the same data

Similarly when I enter data into D2 the same data is copied into G2 & H2

If there is no way of doing this then what i would like to try is a conditional format where each "group" of repeated/duplicated cells would be identified with a different cell colour. I can do a conditional format that highlights each duplicate regardless of the data in the same colour but I can't work out how to apply different colours to each type of repetition.

So again using the example above all cells with a 5100 would be coloured red, all 5102 would be green etc.

 

[petethecat]

I have managed to find this code:



Sub test()
HighlightDuplicates Range("A2:A201")
End Sub

Sub HighlightDuplicates(DuplicateRange As Range)
Dim rng As Range, rngF As Range, rngFull As Range, rngLast As Range
Dim lngColorIndex As Long, varColors As Variant

Set rngFull = DuplicateRange

varColors = Array(3, 4, 5, 6, 7, 8, 9)
lngColorIndex = LBound(varColors)

rngFull.Interior.ColorIndex = xlColorIndexNone
Set rngLast = rngFull(rngFull.Rows.Count, rngFull.Columns.Count)

For Each rng In rngFull
If rng.Interior.ColorIndex = xlColorIndexNone Then
If Application.WorksheetFunction.CountIf(rngFull, rng.Value) > 1 Then
For Each rngF In rngFull
If rngF.Value = rng.Value Then rngF.Interior.ColorIndex = varColors(lngColorIndex)
Next
lngColorIndex = lngColorIndex + 1
If lngColorIndex > UBound(varColors) Then lngColorIndex = LBound(varColors)
End If
End If
Next
End Sub


Has anyone any idea how to increase the number of colours available?

I might need upto 50 different shades & have no idea how to go about it!

 

[MickG]

Have you thought having code to colour the duplicate Module Numbers different colours, then when A Number is added to the list below a particular module number/Colour, then that cell could be coloured the same as the Module Number, and related cells in that line with the same module number could be coloured/Numbered the same value.
This would save a large number of different colours.

 

[MickG]

If you colour your duplicate module Numbers the same colours and then run this code by entering data in one of the cells below you will see what I mean !!!

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Dim[/COLOR] Lst         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K
[COLOR="Navy"]Dim[/COLOR] A           [COLOR="Navy"]As[/COLOR] Range
Application.EnableEvents = False
Lst = Range("A" & Rows.Count).End(xlUp).Row
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Cells(1, Columns.Count).End(xlToLeft))
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Rng.Resize(Lst)) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.Value, Array(Dn, Dn.Resize(Lst))
        [COLOR="Navy"]Else[/COLOR]
          Q = .Item(Dn.Value)
            [COLOR="Navy"]Set[/COLOR] Q(0) = Union(Q(0), Dn)
            [COLOR="Navy"]Set[/COLOR] Q(1) = Union(Q(1), Dn.Resize(Lst))
        .Item(Dn.Value) = Q
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]If[/COLOR] Not Intersect(Target, .Item(K)(1)) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] A [COLOR="Navy"]In[/COLOR] .Item(K)(0).Areas
            A.Offset(Target.Row - 1).Interior.ColorIndex = A.Interior.ColorIndex
            A.Offset(Target.Row - 1) = Target
        [COLOR="Navy"]Next[/COLOR] A
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[petethecat]

I think I see what you are getting at with this...

However i am having an issue that when i enter a digit into the cell it copies the same value into every single cell in the row!

What I want it to do is copy the value I entered into the cell under (lets say for example 5177) and everytime a 5177 appears it copies the value into the cell under the next occurance of 5177.

I might've copied this incorrectly - I rightclicked on the tab & view code / copy past / save / exit

is this what you had in mind?

Appreciate your help

Petethecat

 

[MickG]

Mabe you have a blank row at the top of the sheet that the code is reading.
The layout as below seems to work ok.
NB :-Cells "A1" holds the word Module, with the rest of the data Pro Rata.

<TABLE style="WIDTH: 394pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=525 border=0 x:str><COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: silver" width=77 height=18>MODULE</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: yellow" align=right width=64 x:num>5100</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: aqua" align=right width=64 x:num>44444</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: lime" align=right width=64 x:num>5102</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: aqua" align=right width=64 x:num>44444</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: yellow" align=right width=64 x:num>5100</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: lime" align=right width=64 x:num>5102</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: lime" align=right width=64 x:num>5102</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: silver" width=77 height=35>CANDIDATE 1</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: yellow" align=right width=64 x:num>8</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: aqua" align=right width=64 x:num>46</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: lime" align=right width=64 x:num>7</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: aqua" align=right width=64 x:num>46</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: yellow" align=right width=64 x:num>8</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: lime" align=right width=64 x:num>7</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: lime" align=right width=64 x:num>7</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: silver" width=77 height=35>CANDIDATE 2</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: yellow" align=right width=64 x:num>12</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: aqua" align=right width=64 x:num>36</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: lime" align=right width=64 x:num>46</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: aqua" align=right width=64 x:num>36</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: yellow" align=right width=64 x:num>12</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: lime" align=right width=64 x:num>46</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: lime" align=right width=64 x:num>46</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: silver" width=77 height=35>CANDIDATE 3</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: silver" width=77 height=35>CANDIDATE 4</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: silver" width=77 height=35>CANDIDATE 5</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white"> </TD></TR></TBODY></TABLE>

 

[petethecat]

Aah! - I too can get it to work if i isolate as per your diagram

Trouble is i am stuck as to how to miss the first 7 rows which have heading details.

My row with the module numbers is row 8. I can insert a column that I can use to enter the word "module" this will be column I - in fact this will be cell I8 unless i need to copy this down to I218?

The previous columns (Columns A-H) contain other data such as shift pattern, work centre etc so i need to keep these free.

The data "pool" that i need the code to apply to will be from J9 to WA218. (with J8 to WA8 being the module numbers) So everything outside of this selection would need to be "free" of the code if that is possible?

Thanks again!!
Petethecat

 

[MickG]

Try this:-
Ref:-

The data "pool" that i need the code to apply to will be from J9 to WA218. (with J8 to WA8 being the module numbers) So everything outside of this selection would need to be "free" of the code if that is possible?

Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Rng         [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn          [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Q
[COLOR=navy]Dim[/COLOR] k
[COLOR=navy]Dim[/COLOR] A           [COLOR=navy]As[/COLOR] Range
Application.EnableEvents = False
    [COLOR=navy]Set[/COLOR] Rng = Range("K8:WA8")
[COLOR=navy]If[/COLOR] Not Intersect(Target, Rng.Resize(211)) [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
    [COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        [COLOR=navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=navy]Then[/COLOR]
            .Add Dn.Value, Array(Dn, Dn.Resize(211))
        [COLOR=navy]Else[/COLOR]
          Q = .Item(Dn.Value)
            [COLOR=navy]Set[/COLOR] Q(0) = Union(Q(0), Dn)
            [COLOR=navy]Set[/COLOR] Q(1) = Union(Q(1), Dn.Resize(211))
        .Item(Dn.Value) = Q
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] k [COLOR=navy]In[/COLOR] .keys
     [COLOR=navy]If[/COLOR] Not Intersect(Target, .Item(k)(1)) [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] A [COLOR=navy]In[/COLOR] .Item(k)(0).Areas
            A.Offset(Target.Row - 8).Interior.ColorIndex = A.Interior.ColorIndex
            A.Offset(Target.Row - 8) = Target
        [COLOR=navy]Next[/COLOR] A
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] k
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] If
Application.EnableEvents = True
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[petethecat]

Thanks for this Mick - it works a treat!