Selecting Cells(r,c)

[cmeier7]

Hello, I am trying to get the below code to work. It determines if cells are grayed out and adds gray to a cell of another worksheet. Currently the code stops at the first selection. What is wrong with how I have it called out <SHEETS("" 3).Select ??).Cells(i, & n>?

c = 4
n = "Test_STEEL 5in"
j = 5
For i = 4 To 51
    Sheets("" & n & "").Cells(i, 3).Select
    If ActiveCell.Interior.ColorIndex = 15 Then
        Sheets("Test Results_STEEL").Cells(j, c).Select
        ActiveCell.Value = ""
        ActiveCell.Interior.ColorIndex = 15
    Else
    End If
    j = j + 1
Next i

Thanks in advance for this!

 

[kpark91]

What does the error message say?

If it is subscript out of range, it may be due to Case-sensitive sheet's name

and you can clean up your code by changing to

c = 4
n = "Test_STEEL 5in"
j = 5
For i = 4 To 51
    If Sheets(n).Cells(i,3).Interior.ColorIndex = 15 Then
        Sheets("Test Results_STEEL").Cells(j, c).Value = ""
        Sheets("Test Results_STEEL").Cells(j, c).Interior.ColorIndex = 15
    End If
    j = j + 1
Next i

 

[njimack]

You don't need those quote marks if you've already specified the sheetname as one of you variables, and you don't need to select sheets or cells to perform actions on them

Sub a()
Dim c As Long
Dim n As String
Dim j As Long
Dim i As Long

c = 4
n = "Test_STEEL 5in"
j = 5
For i = 4 To 51


If Sheets(n).Cells(i, 3).Interior.ColorIndex = 15 Then
        With Sheets(c).Cells(j, c)
            .ClearContents
            .Interior.ColorIndex = 15
        End With
    End If
    j = j + 1
Next i
End Sub

 

[dave3009]

Try this

c = 4
n = "Test_STEEL 5in"
r = "Test Results_STEEL"
j = 5
For i = 4 To 51
    If Sheets(n).Cells(i, 3).Interior.ColorIndex = 15 Then
        With Sheets(r).Cells(j, c)
            .Value = ""
            .Interior.ColorIndex = 15
        End With
    j = j + 1
Next i