Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    You don't "change back and forth" as you do when recording a macro; you just reference the desired sheet:
    <pre>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</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

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

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

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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:
    <pre>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</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •