Macro without regional date format dependency

Question

I'm new to VBA programming and I have a problem with the Excel macro that formats tables.

Sub l___API_ENG()


'------------------------------Insert Formula--------------------------------------

       
    Range("F2").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    Selection.End(xlToLeft).Select
    
    ActiveCell.Offset(1, 6).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, -1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, -1).Select
    ActiveCell.FormulaR1C1 = "I"
    
    Selection.AutoFilter
    ActiveCell.CurrentRegion.AutoFilter Field:=5, Criteria1:="I"
    
    Range("G4").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]+R[-2]C-R[-1]C"
    Range("G4").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("F2").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:="=OR(WEEKDAY(F$1;2)=7;WEEKDAY(F$1;2)=6)"
    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:="=AND(F2>0;$E2=""A"")"
    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:="=AND(F2<0;$E2=""I"")"
    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:="I"
    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("F2").Select
End Sub

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?

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?

The table should look like this

Answer 1

Examining your screenshot, it appears that the dates you are testing are column headers of a Table.

That being the case, they are not "real dates" but rather they are text strings that appear as dates.

When your regional settings are congruent, Excel can interpret them as dates, when the settings are not congruent, Excel cannot.

Therefore you have to modify your Formula1 argument to convert these dot separated DMY date strings into "real" dates.

You can do this by adding a UDF to do the conversion to your VBA project, and then using that within Formula1

UDF

Option Explicit
Function dateFromString(strDate) As Date
    Const dtSep As String = "."
    Dim dtParts
    Dim y As Long, m As Long, d As Long
    Dim I As Long
    
dtParts = Split(strDate, dtSep)
    y = dtParts(2)
    m = dtParts(1)
    d = dtParts(0)

dateFromString = DateSerial(y, m, d)
        
End Function

Then you would use it in Formula1 as something like:

FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(LEN($F1)>0;WEEKDAY(datefromstring($F1);2)>5)"

So far as your other question, about using internationally valid formulas for the conditional formatting Formula1 (and Formula2) arguments, you can translate them as demonstrated in the accepted answer to Excel Macro, inserting internationally valid formula during run-time

Especially as a newcomer to VBA, try to get out of the bad habits that the vba macro recorder seems to encourage.

You should get rid of all those selects, selections, activecell, etc. Please read How to avoid using Select in Excel VBA

Also, it is good practice to always declare your variables (although you don't seem to use any in a glance at your code). In the VBA GUI, you can enforce this by Tools/Options/Editor/Code Settings and selecting Require Variable Declaration. This will place OPTION EXPLICIT at the top of each inserted module (and you can also manually add this to modules created before you selected this option). This will be extremely useful in catching typos, variables of the incorrect type, etc.