kevinya

輸出日期格式字串yyyymmdd:
Dim myDate As Variant
myDate = Format(Date, "yyyymmdd")
wsPO.Cells(i, FoundStatus.Column) = myDate


條件式格式設定conditional formatting:
此範例示範當cell value = 'abc'的時候，儲存格的字體顏色是紅色，當cell value = 'bcd'的時候，儲存格的字體顏色是藍色

Dim lastRowPO As Long
lastRowPO = wsPO.Cells(Rows.Count, FoundPartNumber.Column).End(xlUp).Row
Dim rngSite As Range
Dim conditionSite1 As FormatCondition
Dim conditionSite2 As FormatCondition
'Dim conditionSite3 As FormatCondition
Set rngSite = Range(Col_Letter(FoundSite.Column) & "2", Col_Letter(FoundSite.Column) & lastRowPO)
rngSite.FormatConditions.Delete
Set conditionSite1 = rngSite.FormatConditions.Add(xlCellValue, xlEqual, "=""abc""")
With conditionSite1
 .Font.Color = vbRed
End With
Set conditionSite2 = rngSite.FormatConditions.Add(xlCellValue, xlEqual, "=""bcd""")
With conditionSite2
 .Font.Color = vbBlue
End With

公式設定IF:

'用vba設定公式:if有五個選項的時候
wsPO.Range(Col_Letter(FoundORG.Column) & "2:" & Col_Letter(FoundORG.Column) & lastRowPO).Formula = _
"=IF(" & Col_Letter(FoundOU.Column) & "2=""AL1"",""77777"",IF(" & Col_Letter(FoundOU.Column) & _
"2=""AK1"",""88888"",IF(" & Col_Letter(FoundOU.Column) & "2=""3715"",""77777"",IF(" & _
Col_Letter(FoundOU.Column) & "2=""3714"",""88888"",""NA""))))"
'用vba設定公式:if有四個選項的時候
wsPO.Range(Col_Letter(FoundSite.Column) & "2:" & Col_Letter(FoundSite.Column) & lastRowPO).Formula = _
"=IF(" & Col_Letter(FoundPOLine.Column) & "2=""ENG"",""ENG"",IF(" & Col_Letter(FoundPOLine.Column) & _
"2=""ENG03"",""ENG03"",IF(" & Col_Letter(FoundPOLine.Column) & "2="""",""MPG04"",""NA"")))"
'用vba設定公式:if有三個選項的時候
wsPO.Range(Col_Letter(FoundSite.Column) & "2:" & Col_Letter(FoundSite.Column) & lastRowPO).Formula = _
"=IF(" & Col_Letter(FoundPOLine.Column) & "2=""ENG"",""ENG"",IF(" & Col_Letter(FoundPOLine.Column) & _
"2=""ENG03"",""ENG03"",""MPG04""))"
'用vba設定公式:if有2個選項的時候
wsPO.Range(Col_Letter(FoundORG.Column) & "2:" & Col_Letter(FoundORG.Column) & lastRowPO).Formula = _
"=IF(" & Col_Letter(FoundOU.Column) & "2=""AL1"",""10001_02"",""10001_04"")"

取消篩選cancel filter:

If wsPO.FilterMode Then wsPO.ShowAllData
If wsPO.AutoFilterMode Then wsPO.AutoFilterMode = False

參考資料：
https://www.excelcampus.com/vba/copy-paste-cells-vba-macros/
MS Excel 2010: Change the font color based on the value in the cell (techonthenet.com)
VBA Conditional Formatting | Apply Conditional Format using VBA Excel (wallstreetmojo.com)