Excel VBA IF within If Question

[robcor858]

Hello,

I am trying to write a VBA that selects (from the ActiveWorkbook) each sheet that is not named "Engine" Or "Runs". After the sheet is selected, the code will find the word "Total" within my range and will highlight the row from column A:P and go to the next sheet not named "Engine" or "Runs" and perform the same function. I can get the code to work to do one or the other, but not both. If anyone out there knows what I am doing wrong and how to fix it, I would greatly appreciate the help.

Sub Find_Total_Cells()
Dim myRange As Range
Dim myCell As Range
Set myRange = Range("A6:A1000")
For Each myCell In myRange
Dim Sht As Worksheet 'new
For Each Sht In ActiveWorkbook.Worksheets 'new




If myCell Like "*Total*" And Sht.Name <> "Runs" Or Sht.Name <> "Engine" Then
Sht.Select
myCell.EntireRow.Font.Bold = True
myCell.Columns("D:P").Value = "Sum Formula Needed"
With myCell.Columns("A:P").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
End If


Next myCell
Next Sht


End Sub

 

[SQUIDD]

untested, but does this make it work??

Dave

If myCell Like ("*Total*" And Sht.Name <> "Runs") Or myCell Like ("*Total*" And Sht.Name <> "Engine") Then

 

[robcor858]

It does not.

 

[SQUIDD]

ok again untested.

If (Sht.Name <> "Runs" Or Sht.Name <> "Engine") And myCell Like "*Total*" Then

?

Dave

 

[Michael M]

Can you please use code tags when posting code !
If your code was indented it would be easier to read AND debug

This will do what you want

Sub Find_Total_Cells()
Dim myRange As Range, Sht As Worksheet, myCell As Range
For Each Sht In Worksheets 'new
    If Sht.Name <> "Runs" And Sht.Name <> "Engine" Then
        Sht.Activate
        Set myRange = Range("A6:A1000")
        For Each myCell In myRange
            If myCell Like "*Total*" Then
                myCell.EntireRow.Font.Bold = True
                myCell.Columns("D:P").Value = "Sum Formula Needed"
                    With myCell.Columns("A:P").Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorAccent4
                        .TintAndShade = 0.599993896298105
                        .PatternTintAndShade = 0
                    End With
            End If
        Next myCell
    End If
Next Sht
End Sub

 

[Michael M]

@SQUIDD
Dave, using OR will select every sheet regardless of name in this case......you need to use AND instead.
And there is no need for the brackets...

If (Sht.Name <> "Runs" Or Sht.Name <> "Engine")