In Excel 2013 I'm trying to conditionally format a range of values representing Australian dates (dd.mm.yyyy) with periods as delimiters. The values are all formatted as General.
I've recorded a macro to conditionally format all values containing specific text ".04.2015", but in vba where it has Selection.FormatConditions.Add Type:=xlTextString, String:="03.2015",
I'd like to have it use the value of the current month Month (Now)'
and the current year Year(Now)
.
Recorded Code:
Sheets("MM All").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlTextString, String:="03.2015", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16711681
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
What I'm trying for (I've just included relevant code from above)
Sheets("MM All").Select
Dim MNow As String
Dim NMth As String
Dim YNow As String
MNow = Month(Now)
NMth = Month(Now) + 1
YNow = Year(Now)
Range("E2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
If (Unsure how to write) Mnow value is single digit
Selection.FormatConditions.Add Type:=xlTextString, String:="0" & "(MNow)" & "." & "(YNow)",
I'd also include IFs for when Mnow and Nmth values are double digits, where I just wouldn't have the extra 0 concatenated in the string.
Any help would be much appreciated. I'm new to VBA and trying to feel my way around.
Aucun commentaire:
Enregistrer un commentaire