title: Macro without regional date format dependency
body: <p>I'm new to VBA programming and I have a problem with the Excel macro that formats tables.</p>
  <pre><code>Sub l___API_ENG()
  
  
  '------------------------------Insert Formula--------------------------------------
  
         
      Range(&quot;F2&quot;).Select
      ActiveCell.SpecialCells(xlLastCell).Select
      Selection.End(xlToLeft).Select
      
      ActiveCell.Offset(1, 6).Select
      ActiveCell.FormulaR1C1 = &quot;x&quot;
      ActiveCell.Offset(0, -1).Select
      ActiveCell.FormulaR1C1 = &quot;x&quot;
      ActiveCell.Offset(0, -1).Select
      ActiveCell.FormulaR1C1 = &quot;I&quot;
      
      Selection.AutoFilter
      ActiveCell.CurrentRegion.AutoFilter Field:=5, Criteria1:=&quot;I&quot;
      
      Range(&quot;G4&quot;).Select
      ActiveCell.FormulaR1C1 = &quot;=RC[-1]+R[-2]C-R[-1]C&quot;
      Range(&quot;G4&quot;).Select
      Selection.Copy
      Range(Selection, Selection.End(xlToRight)).Select
      ActiveSheet.Paste
      Application.CutCopyMode = False
      
      Selection.Copy
      Range(Selection, Selection.End(xlDown)).Select
      ActiveSheet.Paste
      Application.CutCopyMode = False
      ActiveCell.SpecialCells(xlLastCell).Select
      Range(Selection, Selection.End(xlToLeft)).Select
      Selection.ClearContents
      
      ActiveCell.CurrentRegion.AutoFilter Field:=5
     
  
  '------------------------------Lock Column--------------------------------------
      Range(&quot;F2&quot;).Select
      ActiveWindow.FreezePanes = True
      
  '------------------------------Choose all cells from F2 down and right--------------------------------------
      Range(Selection, Selection.End(xlToRight)).Select
      Range(Selection, Selection.End(xlDown)).Select
      
      
  '------------------------------Set conditional format for the weekend - grey filling--------------------------------------
  
      Selection.FormatConditions.Add Type:=xlExpression, Formula1:=&quot;=OR(WEEKDAY(F$1;2)=7;WEEKDAY(F$1;2)=6)&quot;
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Interior
          .PatternColorIndex = xlAutomatic
          .ThemeColor = xlThemeColorDark1
          .TintAndShade = -0.249946592608417
      End With
      Selection.FormatConditions(1).StopIfTrue = False
      
    '------------------------------Set conditional format for Arrivals - blue filling--------------------------------------
      
      Selection.FormatConditions.Add Type:=xlExpression, Formula1:=&quot;=AND(F2&gt;0;$E2=&quot;&quot;A&quot;&quot;)&quot;
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Interior
          .PatternColorIndex = xlAutomatic
          .Color = 15773696
          .TintAndShade = 0
      End With
      Selection.FormatConditions(1).StopIfTrue = False
      
   '------------------------------Set conditional format for shortage - red filling--------------------------------------
   
          Selection.FormatConditions.Add Type:=xlExpression, Formula1:=&quot;=AND(F2&lt;0;$E2=&quot;&quot;I&quot;&quot;)&quot;
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Font
          .Color = -16383844
          .TintAndShade = 0
      End With
      With Selection.FormatConditions(1).Interior
          .PatternColorIndex = xlAutomatic
          .Color = 13551615
          .TintAndShade = 0
      End With
      Selection.FormatConditions(1).StopIfTrue = False
      
    '------------------------------Clear PN from rows P and I--------------------------------------
      
  
      
     '------------------------------Set underline--------------------------------------
      
  
      ActiveCell.CurrentRegion.AutoFilter Field:=5, Criteria1:=&quot;I&quot;
      ActiveCell.CurrentRegion.Select
      Selection.Borders(xlDiagonalDown).LineStyle = xlNone
      Selection.Borders(xlDiagonalUp).LineStyle = xlNone
      Selection.Borders(xlEdgeLeft).LineStyle = xlNone
      Selection.Borders(xlEdgeTop).LineStyle = xlNone
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .ColorIndex = 0
          .TintAndShade = 0
          .Weight = xlThin
      End With
      Selection.Borders(xlEdgeRight).LineStyle = xlNone
      Selection.Borders(xlInsideVertical).LineStyle = xlNone
      Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
      
      
      ActiveCell.CurrentRegion.AutoFilter Field:=5
      
      Cells.Select
      Cells.EntireColumn.AutoFit
      
      Range(&quot;F2&quot;).Select
  End Sub
  </code></pre>
  <p>The problem is it should paint weekend cells grey and when I have the regional date format set as 21.07.22 it works, but in another case such as 21/07/22 or even 21 July 2022 it doesn't do that.
  By switching date format in windows settings, I found out that the shadow zone just disappears when I switch from the 25.10.21 format. Is there any way to prevent excel from using windows regional settings?</p>
  <p>And is there any function to replace AND and WEEKDAY at conditional format so the macro will be functional for users that use different system languages?</p>
  <p>The table should look like this</p>
  <p><a href="https://i.stack.imgur.com/v4LuV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/v4LuV.png" alt="Table" /></a></p>