Run VBA code from specific sheet till the end of workbook

[Schturman]

Hi
I use this code in module to add a conditional format to sheets:

Option Explicit

Sub RunCF()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Select

Range("$D$7:$H$12,$D$16:$H$21,$D$25:$H$30,$D$34:$H$39,$D$43:$H$48,$D$52:$H$54").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(OR(D7=$H$3,D7=$H$3&""*"",D7=$H$3&""**""),D7>"""")"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        Selection.FormatConditions(1).StopIfTrue = False
         With Selection.FormatConditions(1).Interior
          .PatternColorIndex = xlAutomatic
          .Color = RGB(0, 176, 240)
          .TintAndShade = 0
         End With

 Next ws

End Sub

It working fine, but it add CF to all existing sheets in the workbook.
Can someone change this part of code:

Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Select

that will allow me:
Variant 1 - run code from a specific sheet till the end of workbook
Variant 2 - run code from a specific sheet to another specific sheet. For example run ONLY on 3 sheet "January 04-09", "January 11-16", "January 18-23" or from "January 04-09" till "January 18-23"
Thanks

 

[Sergius]

This should work.
Add

If Left(ws.Name, 13) = "January 04-09" Or Left(ws.Name, 13) = "January 11-16" Or Left(ws.Name, 13) = "January 18-23" Then

before

ws.Select

 

[Skyybot]

I would create a small UserForm that would list all of the Sheets and allow me to choose which Sheets to run the Macro.

 

[AC PORTA VIA]

Sub RunCF_pre_selected()'VARIANT2'
sheetlist = Array("Sheet1", "Sheet3")
For i = LBound(sheetlist) To UBound(sheetlist)
Worksheets(sheetlist(i)).Activate
Dim rng As Range
Set rng = ActiveSheet.Range("$D$7:$H$12,$D$16:$H$21,$D$25:$H$30,$D$34:$H$39,$D$43:$H$48,$D$52:$H$54")

With rng
 .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(OR(D7=$H$3,D7=$H$3&""*"",D7=$H$3&""**""),D7>"""")"
   End With
    With rng.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = RGB(0, 176, 240)
        .TintAndShade = 0
    End With

Next
End Sub
Sub RunCF_from_to_end()'VARIANT1'
Dim sheet As Worksheet

For Each sheet In ActiveWorkbook.Worksheets
  If sheet.Index > 2 Then
 
   sheet.Select
Dim rng As Range
Set rng = ActiveSheet.Range("$D$7:$H$12,$D$16:$H$21,$D$25:$H$30,$D$34:$H$39,$D$43:$H$48,$D$52:$H$54")

With rng
 .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(OR(D7=$H$3,D7=$H$3&""*"",D7=$H$3&""**""),D7>"""")"
   End With
    With rng.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = RGB(0, 176, 240)
        .TintAndShade = 0
    End With
    End If
 Next
    
End Sub

 

[Schturman]

This should work.
Add

If Left(ws.Name, 13) = "January 04-09" Or Left(ws.Name, 13) = "January 11-16" Or Left(ws.Name, 13) = "January 18-23" Then

before

ws.Select

Cool! Thanks ! It working perfectly for specific sheets. Maybe you also know how to do the same for a range of sheets, for example start from "January 04-09" till "May 01-07" ?
I can use your code like this:

If Left(ws.Name, 6) = "January" Or Left(ws.Name, 8) = "February" Or Left(ws.Name, 5) = "March" Then

and so on.. but it's longest way, do you know a shorter way to do this ?
Thanks

 

[Schturman]

Sub RunCF_pre_selected()'VARIANT2'
sheetlist = Array("Sheet1", "Sheet3")
For i = LBound(sheetlist) To UBound(sheetlist)
Worksheets(sheetlist(i)).Activate
Dim rng As Range
Set rng = ActiveSheet.Range("$D$7:$H$12,$D$16:$H$21,$D$25:$H$30,$D$34:$H$39,$D$43:$H$48,$D$52:$H$54")

With rng
 .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(OR(D7=$H$3,D7=$H$3&""*"",D7=$H$3&""**""),D7>"""")"
   End With
    With rng.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = RGB(0, 176, 240)
        .TintAndShade = 0
    End With

Next
End Sub
Sub RunCF_from_to_end()'VARIANT1'
Dim sheet As Worksheet

For Each sheet In ActiveWorkbook.Worksheets
  If sheet.Index > 2 Then
 
   sheet.Select
Dim rng As Range
Set rng = ActiveSheet.Range("$D$7:$H$12,$D$16:$H$21,$D$25:$H$30,$D$34:$H$39,$D$43:$H$48,$D$52:$H$54")

With rng
 .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(OR(D7=$H$3,D7=$H$3&""*"",D7=$H$3&""**""),D7>"""")"
   End With
    With rng.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = RGB(0, 176, 240)
        .TintAndShade = 0
    End With
    End If
 Next
  
End Sub

Thank you ! Variant 1 worked, I needed only one line: If sheet.Index > 2 Then
Variant 2 not working... Don't know why...

 

[Schturman]

Ok, After some tries this part is also working fine:

Sub RunCF_pre_selected()'VARIANT2'
sheetlist = Array("Sheet1", "Sheet3")
For i = LBound(sheetlist) To UBound(sheetlist)
Worksheets(sheetlist(i)).Activate

but only for specific sheets that I wrote, for example "January 04-09" and "May 01-07" and not between them..

Big thanks to all !

 

[Alex Blakenburg]

If between means in terms of their position in the workbook, you could use this structure.

Sub RunCF_between_sheets() 'VARIANT3'
    Dim FromSht As String, ToSht As String
    Dim i As Long

    FromSht = "January 04-09"
    ToSht = "May 01-07"
    
    For i = Worksheets(FromSht).Index To Worksheets(ToSht).Index
        Debug.Print Worksheets(i).Name
    Next i
End Sub