Populate data on another worksheet

[y2gordo]

I'm sure this question has been answered numerous times, but brain farts are preventing me from finding it.

I'm trying to create a macro that will cell-by-cell look at a range of data on Sheet1 and conditionally apply a "1" to Sheet2 and copy the cell to Sheet4. I've gotten this to work by doing

    For CountRow = 1 To Size
        For CountCol = 1 To Size
            If ActiveCell.Interior.ColorIndex = 3 Then
                Sheets("Sheet2").Select
                ActiveCell.Value = 1
                ActiveCell.Offset(0, 1).Select
                Sheets("Sheet4").Select
                ActiveCell.Interior.ColorIndex = 3
                ActiveCell.Offset(0, 1).Select
                Else
                    Sheets("Sheet2").Select
                    ActiveCell.Offset(0, 1).Select
                    Sheets("Sheet4").Select
                    ActiveCell.Offset(0, 1).Select
                End If
                
                Sheets("Sheet1").Select
                ActiveCell.Offset(0, 1).Select
                
            Next CountCol
        
        Sheets("Sheet2").Select
        ActiveCell.Offset(1, (Size * -1)).Select
        Sheets("Sheet4").Select
        ActiveCell.Offset(1, (Size * -1)).Select
        Sheets("Sheet1").Select
        ActiveCell.Offset(1, (Size * -1)).Select
        
        Next CountRow

However, I'm sure there's easier (thus faster) way to do this without having to jump from sheet to sheet. I know I can populate the ActiveCell that will detect a value on another sheet using relative coordinates (ActiveCell.FormulaR1C1 = "='Sheet2'R[-1]C[-1]", for example), but I haven't figured out a way to do this using things that aren't Worksheet formulas.

I tried doing:

    For CountRow = 1 To Size
        For CountCol = 1 To Size
            If ActiveCell.Interior.ColorIndex = 3 Then
                Range("Sheet2!RC").Value = 1
                End If
            ActiveCell.Offset(0, 1).Select
            Next CountCol
        ActiveCell.Offset(1, (Size * -1)).Select
        Next CountRow

Attempting to only have to offset the cell on Sheet1, but that didn't work. However, I think I'm on the right path. Any help would be greatly appreciated. Thanks.

[antoka05]

You can try with this code:

Sub Macro1()

    For CountRow = 1 To sh1.Size
        For CountCol = 1 To Size
            If Sheets("Sheet1").Cells(CountRow, CountCol).Interior.ColorIndex = 3 Then
                Sheets("Sheet2").Cells(CountRow, CountCol).Value = 1
                Sheets("Sheet4").Cells(CountRow, CountCol).Interior.ColorIndex = 3
            End If
        Next CountCol
    Next CountRow
      
End Sub

Where the 'Cells' instruction is to use: Cell (row, column).
For instance:
cells(3,5) or cells(3,"e") referes to range 'E5'.

I hope it can help you.

Regards,
Antonio

[antoka05]

I'm sorry for my error:
Cells(3,5) or cells(3,"e") refers to range 'E3' and not to range 'E5'.

[y2gordo]

Perfect! Exactly what I was looking for, and works like a charm. Thank you.