highlight duplicates from one sheet to another

[nair.harish]

I have 15 sheets with variable data and the sheet names keep changing which i normally consolidate using pivot tables using multiple consolidation ranges and combine it into one sheet. what I really want to do is if i highlight a particular line on the consolidated sheet, then the same lines have to be highlighted across the rest 15 sheets.
Is there a vba or a conditional formatting option to do this?

Well just to make things a little more clear, i have uploaded the workbook at http://www63.zippyshare.com/v/2660780/file.html, there are only 3 sheets (sheet name keeps changing and there can be upto 100 sheets at one time) in it with one consolidated sheet, and i have highlighted one line on the consolidated sheet which i have again highlighted on the others.

Now if someone could figure out a vba to make it happen automatically i really would be extremely thankful

I am using excel 2007.

 

[baitmaster]

This code will colour red, the same range on every worksheet. Select the range, and run the macro. Change the colorindex (or color) to suit your needs

Sub colourAll()
Dim i As Integer
For i = 1 To Worksheets.Count
    Worksheets(i).Range(Selection.Address).Interior.ColorIndex = 3
Next i
End Sub

 

[nair.harish]

Thank you so much for the response.

Well....Its only colouring the same range in every sheet, i want it to check the lines which are coloured on the consolidated sheet thats sheet8 for now, then colour the same lines on other sheets.

 

[baitmaster]

I'm not sure of an easy / quick way to do what you want that doesn't have some set of problems with it

The method provided assumes you will colour them all at the same time, you are instead asking to do the second set of colouring retrospectively. The problem is identifying which ranges are affected, and what colour to change them to, both of which were simplified in my method by hard-coding the colour, and letting the user select the range

You could copy the entire sheet, and pastespecial the formats, but this will take more than just colours over - it will take number formats, borders, merged cells etc. If this is not a problem it will be your best approach

Alternatively you could loop through every cell in your used range, then copy the color to each other sheet like in my original code - that could be slow though

 

[nair.harish]

My basic requirement here is that on Sheet8 i would be highligting the lines by myself, all i want the macro to do is look at sheet8 for which lines are highlighted and highlight the same lines on the other sheets if those lines are availaible (quantity column need not be considered, just need to match the name). If the macro could do that it would be really helpful.

Thanks a lot for trying.

 

[baitmaster]

This variation looks for any coloured cell in column 1 (including white, just not blank colour), and copies that colour to the same cells on every other worksheet. Entirerow (highlighted) extends the feature to, yes, the entire row, you can remove this if you don't want it

Sub replaceColours()
Dim cl As Range
Dim CI As Long
Dim i As Integer
Dim strAdd As String
 
For Each cl In ActiveSheet.UsedRange.Columns(1).Cells
    strAdd = cl.Address
    CI = cl.Interior.Color
    If CI <> 16777215 Then
        For i = 1 To Worksheets.Count
            Worksheets(i).Range(cl.Address)[COLOR=red].EntireRow[/COLOR].Interior.Color = CI
        Next i
    End If
Next cl
 
End Sub

 

[nair.harish]

Thank you so much it works great, heres the problem.

It only highlights line 5 across every sheet.

I wish what it could do is, check if "Adcal 1500mg chewable tablets (ProStrakan Ltd) 100 tablet 10 x 10 tablets" (this line is manually highlighted by me on sheet8 when i run the macro it should basically highlight "Adcal 1500mg chewable tablets (ProStrakan Ltd) 100 tablet 10 x 10 tablets" if its availaible on the other 3 sheets and if yes then highlight the same.) is highlighted on sheet8 then highlight the same on other sheets.

 

[baitmaster]

Right OK, I had misunderstood your original requirement. Try this instead, you can insert the entirerow bit again if you want...

Sub replaceColours()
Dim cl As Range
Dim CI As Long
Dim i As Integer
Dim strText As String
For Each cl In ActiveSheet.UsedRange.Columns(1).Cells
    CI = cl.Interior.Color
    If CI <> 16777215 Then
        strText = cl.Value
        For i = 1 To Worksheets.Count
            
            ' find next instance of strText
            Dim rngNext As Range: Set rngNext = Worksheets(i).Cells.Find(What:=strText, After:=ActiveCell, LookIn:= _
                xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
            
            ' if found, colour it
            If Not rngNext Is Nothing Then rngNext.Interior.Color = CI
            
        Next i
    End If
Next cl
End Sub

 

[nair.harish]

Thanks a million for helping me out. It works like a charm.

I have a few questions.

How to assign a shortcut key for the above macro?

Will it have a different effect if used on other combination of sheets?

Do I have to select a particular range before i run the macro?

Thanks for helping me out with this.

 

[baitmaster]

Cool, glad we got there, it's quite an interesting feature which I'm sure will be reused quite a bit

To assign a shortcut key in XL07, you want View > Macros > View Macros > [select the macro] > options

It starts on the active sheet, then copies details of any colours found to all other sheets containing the same data. It is therefore important that you start on the right sheet, and that it has some data on it, i.e. the usedrange exists. You also need to restrict acces to any sheet that you dont want colour to apply to, its quite general at the moment

You may need additional error handling in case you have colour in an empty cell somewhere

The code refers to the used range of the active sheet, you could change this to a different range if you wanted, such as a specific set of cells, or to "Selection"