Highlighting the same rows in multiple sheets 1

[pukiamak]

Hello all,

I am newbie to this forum. I have 3 sheets in excel. I have a problem in creating macros to select rows which have the same content/variable among the 3 sheets. Anyone can help me with the code? i would really appreciate it.

 

[firefytr]

Hi there,

First of all, there's no real need to Select anything. If you could tell us what exactly you're wanting to do, along with any criteria or (explained) data structure relevant, we could probably come up with an optimized solution. Please post anything you've tried as well.

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci

 

[pukiamak]

Hi firefytr,

here is the thing. I have 3 sheets of data in excel. I want to know which rows have the same data/content among all the three sheets. I'm new to VBA and I do not know where to start from.

thanks a bunch firefytr

 

[firefytr]

Is there a certain range to check? Is it any data in any cell of any sheet? What are the sheets, Sheet1, Sheet2 & Sheet3? Do you have any conditional formatting on any cells in any of the sheets, or any event code in any of the sheets? And do you want to highlight the entire row?

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci

 

[pukiamak]

Hi firefytr,

I just want to check if the content of the cell is exactly the same in all 3 sheets. yes, there is data (String) in the cells. Yes, the sheets are sheet 1,2 and 3. No, I do not have any conditional formatting. In every sheet, there are only pure data. Yes. I want to highlight row which is identical among the 3 sheets.

Thx,
Nic

 

[firefytr]

Nic, you might be able to use something like this ...

Option Explicit

Private Const MYSHEETS As String = "Sheet1,Sheet2,Sheet3" 'enter all sheets to check here

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim arrWs() As String, i As Long, n As Long
    If Target.Cells.Count > 1 Then Exit Sub
    If Len(Target.Value) = 0 Then Exit Sub
    arrWs = Split(MYSHEETS, ",")
    For i = LBound(arrWs) To UBound(arrWs)
        If Target.Parent.Name <> arrWs(i) Then
            If WorksheetFunction.CountIf(Me.Sheets(arrWs(i)).Cells, Target) > 0 Then
                Target.EntireRow.Interior.ColorIndex = 6
                Exit For
            End If
        End If
    Next i
End Sub

This code goes into your ThisWorkbook module. Post back if it doesn't work for you (did for me in testing) or if you need more help.

HTH

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[pukiamak]

Hi firefytr,

maybe this is a stupid question. i have put the code in This Workbook but it seems it didn't work. How do i do it?

another question is i have my code declared as

Private shtc(1 To 3) As String
sub macro4()
dim row as integer
Do While Sheets(shtc(1)).Range("A1").Cells(row, 1).Value <> ""

why every time i try to run it, it gives me an error saying run time error '9' subscript out of range.

thx a bunch

 

[firefytr]

I'm not really sure what you're trying to do with that code. In my tests, the codeI posted worked fine for me. Maybe you can set a breakpoint early on in your code (put your cursor in the desired line and press F9 {no compiled lines, i.e. Dim statements}). Then select a cell whose value you know is in one of the other sheets and press F2, then enter, you should now be in break mode (VBE opens, breakpoint line highlighted, it acts like the Stop statement). Press F8 to step through your code and see what's going on. Post back with what you find.

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[pukiamak]

Hi firefytr,

Sorry for asking you quest again. So now, I created a new macro and a module come out. I copy paste your code and when i try to run it it does not like it. Could you teach me step by step please to run it?

Thx,
Nic

 

[firefytr]

Hi there, no problem at all. The code is a worksheet event and needs to go into the ThisWorkbook module and not a standard module (even though it's a sheet event, it's workbook-wide).

Is there any other code at all in your ThisWorkbook module? If so post it. If not, try what I posted earlier (15 Jul 06 18:14) about the breakpoint.

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[pukiamak]

Hi firefytr ,

i tested your code and it works only if i type/input data into the sheet. In my case, I already have sets of data which need to be checked to find the identical among the three. I do not know how to modify your code.

thx,
Nic

 

[firefytr]

Okay, well if you keep that VBA in place it will work for all future occurances (if you want). You'll need to run something like this ...

Option Explicit

Const MYSHEETS As String = "Sheet4,Sheet2,Sheet3"    'enter all sheets to check here

Sub CheckThreeSheets()
    Dim ws As Worksheet, c As Range
    Dim arrWs() As String, i As Long, n As Long
    arrWs = Split(MYSHEETS, ",")
    Application.ScreenUpdating = False
    For i = LBound(arrWs) To UBound(arrWs)
        For Each c In Sheets(arrWs(i)).UsedRange
            For n = LBound(arrWs) To UBound(arrWs)
                If c.Parent.Name <> arrWs(n) Then
                    If WorksheetFunction.CountIf(Sheets(arrWs(n)).Cells, c) > 0 Then
                        c.EntireRow.Interior.ColorIndex = 6
                        Exit For
                    End If
                End If
            Next n
        Next c
    Next i
    Application.ScreenUpdating = True
End Sub

This code needs to go into a Standard Module (from the VBE, Insert | Module). It may take a while to run, it will loop through every cell of every sheet, then check every cell of every other sheet in each said iteration (three in all) to make sure all cells are covered.

HTH

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[pukiamak]

Hi firefytr,

Thanks a lot for your code. It works perfectly but i do have one question when i try to run it on large data, it seem to crash out. In my case, I have around 400 rows per sheet. Do you know why?

Thx,
Nic

 

[firefytr]

You may be able to speed it up a little bit by adding a few other lines to it, but I wouldn't expect it to be that much. It's just a lot of looping that you're wanting to do ...

Option Explicit

Const MYSHEETS As String = "Sheet4,Sheet2,Sheet3"    'enter all sheets to check here

Sub CheckThreeSheets()
    Dim ws As Worksheet, c As Range
    Dim arrWs() As String, i As Long, n As Long, LastRow As Long, LastCol As Long
    arrWs = Split(MYSHEETS, ",")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    For i = LBound(arrWs) To UBound(arrWs)
        LastRow = Sheets(arrWs(i)).Cells.Find("*", searchorder:=xlByRows).Row
        LastCol = Sheets(arrWs(i)).Cells.Find("*", searchorder:=xlByColumns).Column
        For Each c In Sheets(arrWs(i)).Range("A1", Sheets(arrWs(i)).Cells(LastRow, LastCol))
            For n = LBound(arrWs) To UBound(arrWs)
                If c.Parent.Name <> arrWs(n) Then
                    If WorksheetFunction.CountIf(Sheets(arrWs(n)).Cells, c) > 0 Then
                        c.EntireRow.Interior.ColorIndex = 6
                        Exit For
                    End If
                End If
            Next n
        Next c
    Next i
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP? Professional Office Developers Association
- Leonardo da Vinci

 

[pukiamak]

Hi firefytr,

Thanks a lot for the code. It really help me. I really appreciate it. Now, I have another question. Is that fine with you? Let's say on

Sheet 1, in cell (1,1) I have the entry "Good,Morning,Sir"

On sheet 2, in cell (1,1) I have the entry ("Good,Afternoon,Sir).

I want to traverse inside the cell so that if there is a similar data like above(Good & Sir), I want to remove both of them. how do i traverse the content of the cell, in my case the data inside the cell is separated by comma.

Thx,
Nic

 

[firefytr]

In all honesty, you'd be better off starting a new thread and not keeping this together. Either way you choose, please provide:

Do you always want to check each part of the comma?
Are you wanting to remove all parts that match anywhere else?
Please provide some examples of how this would work for you, approx. 5-10 sets of data.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci