dimanche 29 mars 2015

Conditional formatting in Excel VBA using the current month and year values in the format conditions

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