Change Color instead of value in VBA

[andrewb90]

Hello all,
I was hoping that info from the macro recorder would help, but it didn't do so well.

Here's what I have:

       With Sheets("Scheduler")                 .Cells(I + 85, j + 2).Value = "hi"
                '.Pattern = xlSolid
                '.PatternColorIndex = xlAutomatic
                '.ThemeColor = xlThemeColorDark1
                '.TintAndShade = -0.349986266670736
                '.PatternTintAndShade = 0
               End With

The commented out lines are what I would like to use, instead of just changing the text value. But putting them in doesn't work. I have this funny feeling it's pretty simple, its just in a different order than I got from recording...

Thanks,

Andrew

 

[mrmmickle1]

Are you trying to do this by chance?:

[COLOR=#0000ff]Sub[/COLOR] Test()
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]    With [/COLOR]Cells(1, 1)[B][COLOR=#ff0000].Interior[/COLOR][/B]
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
        .PatternTintAndShade = 0
[COLOR=#0000ff]    End With[/COLOR]
[COLOR=#0000ff]    [/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

 

[andrewb90]

I should have posted the entire code in the original post. I need to have the specific sheet in the code as the if statement is looking at a separate sheet. All I got was an error stating interior method of range class failed.

Here's the entire code:

Sub Reverse() 'all we need is to figure out how to put a color instead of a value and it should work! 6/5/16Dim I As Integer
Dim j As Integer




For I = 1 To 207
    If I = 88 Or I = 107 Or I = 126 Or I = 145 Or I = 164 Or I = 193 Then 'skipped rows
        GoTo Next1
    Else
        For j = 3 To 16 Step 2 ' j is column #
            If Sheets("RO").Cells(I, j).Value = "0" Then
               
               With Sheets("Scheduler")
                .Cells(I + 85, j + 2).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.349986266670736
                .PatternTintAndShade = 0
               End With
            
            End If
        Next j
    End If
Next1:
Next I
End Sub

 

[mrmmickle1]

Change this portion like I previously mentioned:

[COLOR=#0000ff]With[/COLOR] Sheets("Scheduler").Cells(I + 85, j + 2).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.349986266670736
    .PatternTintAndShade = 0
[COLOR=#0000ff]End With[/COLOR]

I just tested this in 2010. Works without issue.

 

[andrewb90]

awesome! thanks!

 

[mrmmickle1]

Your welcome.