Macro de Excel VBA:

Quiero dividir trimestralmente las dates de un año (con algunos colors).

En este momento tengo una macro solo por 2 años, pero quiero hacerla automáticamente cada año. (por ejemplo: con el color rojo: entre la date 01/01 y YYYY-01/05 / YYYY ….. y así sucesivamente)

Esto es lo que hizo:

Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Rows("1:1").Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="01/01/2015", Formula2:="01/05/2015" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.799981688894314 End With Rows("1:1").Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="30/04/2015", Formula2:="01/08/2015" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 7461287 .TintAndShade = 0 End With Rows("1:1").Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="31/07/2015", Formula2:="01/01/2016" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 6750207 .TintAndShade = 0 End With Rows("1:1").Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="01/01/2016", Formula2:="01/05/2016" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.799981688894314 End With Rows("1:1").Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="30/04/2016", Formula2:="01/08/2016" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 7461287 .TintAndShade = 0 End With Rows("1:1").Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="31/07/2016", Formula2:="01/01/2017" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 6750207 .TintAndShade = 0 End With End Sub 

El resultado: ingrese la descripción de la image aquí

Ahh, es ese caso que necesitas esto:

  Sub demo() Dim r As Range For Each r In ActiveSheet.UsedRange.Columns(1).Cells If IsDate(r) Then Select Case Month(r) Case 1 To 3 With r.Interior .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.799981688894314 End With Case 4 To 6 r.Interior.Color = 7461287 Case 7 To 8 r.Interior.Color = 7461287 Case 9 To 12 r.Interior.Color = 6750207 End Select End If Next r End Sub 

Al comienzo del código agrega

  Private Sub CheckBox1_Click() Const DesinetworkingYear = "2017" 'change this for each year you want 

Luego cambie líneas como esta:

  Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="01/01/2015", Formula2:="01/05/2015" 

a

  Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="01/01/" & DesinetworkingYear, Formula2:="01/05/" & DesinetworkingYear