Find cell and shade it in another wksheet (Excel xp/win 2000)

Topic

New Reply

I have one workbook with data in it. Column C may or may not have a “b” in it. If it does I need to make a cell in another worksheet in another workbook yellow.

Range(“C235:C244″).select ‘in workbook 2
Application.ReplaceFormat.Interior.ColorIndex = 36 ‘in workbook 1
Selection.Replace What:=”b”, ‘in workbook 2

Where do I change back and forth from one worksheet to another? Thank you for the help.

Reply | Quote

Replies

You don’t “change back and forth” as you do when recording a macro; you just reference the desired sheet:

Option Explicit
Sub Macro1()
Dim i As Integer
    For i = 235 To 244
        If Sheets("Sheet2").Cells(i, 3) = "b" Then
            Sheets("Sheet1").Cells(i, 1).Interior.ColorIndex = 36
        Else
            Sheets("Sheet1").Cells(i, 1).Interior.ColorIndex = xlNone
        End If
    Next i
End Sub

Reply | Quote

Ok, thanks. What if they are in 2 different workbooks?

Reply | Quote

Same principle, you preface the Sheets… with an item from the Workbooks collection. Note that both workbooks must be open. Since it gets a little messy with all those collections, I usually create Worksheet objects for each of my sheets. In this example the book with the b’s has filename Reference.xls and the book to be yellowed is Update.xls:

Option Explicit
Sub Macro1()
Dim wsRef As Worksheet
Dim wsUpdate As Worksheet
Dim i As Integer
    Set wsRef = Workbooks("Reference.xls").Sheets("Sheet1")
    Set wsUpdate = Workbooks("Update.xls").Sheets("Sheet1")
    For i = 235 To 244
        If wsRef.Cells(i, 3) = "b" Then
            wsUpdate.Cells(i, 1).Interior.ColorIndex = 36
        Else
            wsUpdate.Cells(i, 1).Interior.ColorIndex = xlNone
        End If
    Next i
    Set wsRef = Nothing
    Set wsUpdate = Nothing
End Sub

Reply | Quote