Formula on 3rd sheet that maps to 1st two sheets corresponding cells

[j0coder]

A simple explanation of what I am trying to accomplish is this:

We have a .csv file that has a bit of post processing done on it. We take these 2 files and put them in 2 separate sheets within the same workbook in excel (done through VBScript). I then create a 3rd sheet and my goal is to write the Formula (=IF('Sheet1'!A1,A2,B1,B2,ect='Sheet1 (2)'!A1,A2,B1,B2,ect, 'TRUE', 'FALSE') into each cell of the 3rd sheet based off of the .UsedRange of the other 2 sheets.

I am able to access the .UsedRange of the 1st sheet and can manipulate the cell doing something like:

compareSheet.Range[COLOR=#000000]([/COLOR]tgtWorksheet.UsedRange.Address[COLOR=#000000])[/COLOR].Interior.ColorIndex [COLOR=#000000]=[/COLOR] [COLOR=#800000]4[/COLOR]

I have tried something similar to:

For Each cell In tgtWorksheet.UsedRange
    compareSheet.Range(cell).Interior.ColorIndex = 4
Next

But I am at a lost when it comes to accessing the correct cells from the other sheets, and can't wrap my head around how to use a loop to manipulate the formula.

 

[j0coder]

any and all help/suggestions/alternative ways to do this are greatly appreciated

 

[j0coder]

bump still need help

 

[Joe4]

So, you are trying to compare like cells on the two sheets (compare A1 on first sheet, to A1 on second sheet, then A2 on first sheet to A2 on second sheet, etc)?
And then you want to highlight the ones that don't match?
Is that correct?

Which sheet would this highlighting be happening on (one or both)?
How many columns are on these sheets what we need to compare?

 

[j0coder]

That is correct, and then the third sheet will contain either the value "true" or "false" depending if the cells of sheet 1 and sheet 2 match. I have the formula that does the trick but I am not sure how to go about looping through the correct cells(based on the other sheets)

 

[j0coder]

The number of rows will be different every time we will need this to be executed, but the number of columns should be the same each time if that helps

 

[Joe4]

Is it safe to assume that each sheet may have a different number of rows?
What about columns? Are there always a set number of columns?

 

[j0coder]

The first 2 sheets will have the same number of rows and columns, and ideally match exactly (that's the purpose of this script) Number of columns will be set every time and shouldn't change but the number of rows each day we need to run this will be different

 

[Joe4]

OK. I was working on some script that actually looks at the number of columns and rows and each sheet, and take the maximum, and creates that many comparison formulas on the third page.
So this script will populate your third sheet with all the formulas. The only thing you might need to change are the sheet names.

Sub MyMacro()

    Dim dws1 As Worksheet
    Dim dws2 As Worksheet
    Dim cws As Worksheet
    
    Dim lCol1 As Long
    Dim lCol2 As Long
    Dim lRow1 As Long
    Dim lRow2 As Long
    Dim maxCol As Long
    Dim maxRow As Long
    Dim frm As String
    
'   Designate two data worksheets to compare
    Set dws1 = Sheets("Sheet1")
    Set dws2 = Sheets("Sheet2")
'   Designate comparison sheet
    Set cws = Sheets("Sheet3")

'   Find last rows and columns on two sheets
    lCol1 = dws1.Range("A1").SpecialCells(xlLastCell).Column
    lRow1 = dws1.Range("A1").SpecialCells(xlLastCell).Row
    lCol2 = dws2.Range("A1").SpecialCells(xlLastCell).Column
    lRow2 = dws2.Range("A1").SpecialCells(xlLastCell).Row
    
'   Find maximum column number
    If lCol1 > lCol2 Then
        maxCol = lCol1
    Else
        maxCol = lCol2
    End If
    
'   Find maximum row number
    If lRow1 > lRow2 Then
        maxRow = lRow1
    Else
        maxRow = lRow2
    End If

'   Enter formulas on third sheet
    frm = "=" & dws1.Name & "!RC=" & dws2.Name & "!RC"
    cws.Activate
    cws.Range(Cells(1, 1), Cells(maxRow, maxCol)).FormulaR1C1 = frm
    
End Sub