Referencing ranges from another worksheet

[JessWhyCuh]

Hello all,

I am trying to read the style of the cells in another worksheet to highlight cells in the active worksheet. So far I have the code below checking the first row, but I need it to go through all the rows (r) then check the cells in each column of that row (col).

Private Sub Worksheet_Activate()
        For i = 1 To r
            For j = 1 To col
                If Worksheets("Prod Lifecycle").Range("J5").Cells(1, j).Style = "Bad" Then
                    Worksheets("Summary").Range("H3").Style = "Bad"
                End If
            Next j
        Next I
End Sub

Thanks in advance!

 

[offthelip]

Using a "style" as a boolean flag is not a very good way to design a system. One of the main drawbacks of using style like this is that it will make your workbook very slow. It would appear that every time you active this sheet you want to check all the cells in another sheet and copy the style to yet another sheet. Waht you should do if there is a condition to check for is check the data for the condition not the style. When I actually looked at your code there are a number of problems even if you want to persist with this routine which will slow down your workbook .
to get your code to work you need to make a number of modifications:

Private Sub Worksheet_Activate()
        ' r is undefine so you must define the lastrow that you want checked in some way such as 
         r=100
         ' col is undefine so you must define col to be the last column you check 
         For i = 1 To r
         col=10          
          For j = 1 To col
                ' you need to change the reference in your if  statemnt to something which references a range correctly 
'                If Worksheets("Prod Lifecycle").Range("J5").Cells(1, j).Style = "Bad" Then
                If Worksheets("Prod Lifecycle").Range(cells(i,j),Cells(i, j)).Style = "Bad" Then
                    ' if you want to change where the style is copied to you need to change this to some range refence that uses i and j  as above
                    Worksheets("Summary").Range("H3").Style = "Bad"
                End If
            Next j
        Next I
End Sub

 

[JessWhyCuh]

Thanks for the tip! A little background of my project, it's a tracking tool which changes the style of a cell to "Bad" if the date of that cell is a past date. Once the user reviews and makes an edit, it is no longer in the style "Bad" that is why I have the style as a Boolean. The Summary worksheet is for manage for a quick look to see which products are off track. Also, my apologies I thought I included the defined variables r and col.

The line If Worksheets("Prod Lifecycle").Range(Cells(i, j), Cells(i, j)).Style = "Bad" Then is giving me a Run-time error '1004': Application-defined or object-defined error

 

[JessWhyCuh]

Disregard my error message, I fixed it. However, is there a better way instead of looping through each column that I can find the first cell styled "Bad"?

**EDIT** I added an Exit For.

Private Sub Worksheet_Activate()
    r = Application.WorksheetFunction.CountA(Worksheets("Prod Lifecycle").Range("B5:B200"))
    col = Application.WorksheetFunction.CountA(Worksheets("Prod Lifecycle").Range("J1:AO1"))
        For i = 1 To r
            For j = 1 To col
                If Worksheets("Prod Lifecycle").Range("J5").Cells(i, j).Style = "Bad" Then
                    Worksheets("Summary").Range("H3").Style = "Bad"
                    Exit For
                End If
            Next j
        Next i
End Sub

But now I am getting an error message, Run-time error '450': Wrong number of arguments or invalid property assignment with the worksheets("summary").range(.....................) line

 

[offthelip]

it's a tracking tool which changes the style of a cell to "Bad"

Just as I thought, not a good design. and it is causing problems for you . If I was doing this I would change it, if you are storing data then it should be stored as data , storing it as a format falls down as soon as you print it, because it might be black and white printer or a printer with a limited font.

to fix you error problem you need to use debug because the code works perfectly well on my sheet once i have changed the names. Use debug to find out what the values of I and J are when it fails and then check on the worksheet what you have got in those cells.