3

i have a calendar in Excel which continues until July 2020. What i would like to have in my worksheet is an automatic coloration of all cells of the "current month" every time i open the workbook.

I did already the "current day auto-coloration" This is the code : 

Public Sub FormattaCalendario()

  Dim LColCount As Long
  Dim cell As Variant

  ultimoGiorno = DateSerial(Year(Date), Month(Date), 0)
  primoGiorno = ultimoGiorno - Day(ultimoGiorno) + 1

  LColCount = Cells(TrovaInizioProgetti(activeCell) - 1, 
  Columns.Count).End(xlToLeft).Column

  For Each cell In Range(Cells(TrovaInizioProgetti(activeCell) - 1, 11), 
  Cells(TrovaInizioProgetti(activeCell) - 1, LColCount))

    If cell.Value = Date Then
      cell.Interior.Color = RGB(255, 255, 91)
    End If

    If CDate(cell.Value) <> Date Then
      cell.Interior.Color = RGB(255, 150, 0)
    End If

  Next

End Sub

Thank you in advance.

Lomoo
  • 47
  • 7
  • You are only taking the day you are in, instead you need to use `If Month(cell) = Month(Date) then` that way you pick up the month. – Damian Sep 25 '19 at 08:29
  • Something like : `If cell.Value = Month(Date) Then cell.Interior.Color = 'Color End If` – Lomoo Sep 25 '19 at 08:34
  • No, because `cell.value` is the date, not the month... you need to use the `Month()` for both comparisions. Look at the answer below. – Damian Sep 25 '19 at 08:36

2 Answers2

4

This can be done a lot easier with conditional formatting. Apply this formula to the range where your dates are in:

=MONTH(A1)=MONTH(NOW())

This will highlight every cell within that range which has a date within the current month. Make sure to change "A1" to the first cell within that range. Or if you want the entire row highlighted, enter $*Column*1 instead, where *Column* is the column your dates are in. Then apply to your entire sheet.

EDIT As I still believe in this answer more than the VBA option I'll also answer the questions in the comments on that answer:

Todays date can be highlighted with the formula:

=A1=TODAY()

EDIT 2 After some trouble and learning on my part the best formula to highlight the weekend days is as so:

=IF(CELL("format",A1)="D1",WEEKDAY(A1,2)>5,FALSE)

With thanks to @shrivallabha.redij and @Foxfire And Burns And Burns

Plutian
  • 2,276
  • 3
  • 14
  • 23
  • This is totally unnecessary Plutian, you have the `Month()` which is much shorter and clean. – Damian Sep 25 '19 at 08:40
  • That is indeed a cleaner way to do it, and gives the same result. I'll update my answer. I forgot about the `Month()` since I was more concerned about getting my point across about doing this with conditional formatting. – Plutian Sep 25 '19 at 08:42
  • 2
    Whilst CF is volatile, I think I would still prefer it over a more static cell color through VBA. =) – JvdV Sep 25 '19 at 08:47
  • @JvdV agreed, if anything it should be considerably faster, negating the need to trigger a macro on every workbook open or change. – Plutian Sep 25 '19 at 08:57
2

This replacement should work for what you're requesting:

If Month(CDate(cell.Value)) = Month(Date) Then
  cell.Interior.Color = RGB(255, 255, 91)
Else
  cell.Interior.Color = RGB(255, 150, 0)
End If

Note that you don't need two separate IF statements for this.

riskypenguin
  • 2,139
  • 1
  • 10
  • 22
  • Okay, it worked, thanks a lot. One last question, to color also the current day? – Lomoo Sep 25 '19 at 08:39
  • @Lomoo put the one you had with `If` and checking the month a `ElseIf` – Damian Sep 25 '19 at 08:42
  • Ok, i forgot also an option to color saturdays and sundays, is that possible? – Lomoo Sep 25 '19 at 08:45
  • Which one overrides, today or saturday and sunday? – Damian Sep 25 '19 at 08:47
  • All three of them. – Lomoo Sep 25 '19 at 08:49
  • 1
    So if today is saturday, which colour needs to be shown? Because I believe you want today in a colour, saturdays and sundays in a different colour and the rest of the month in a different colour. So which one is it? – Damian Sep 25 '19 at 08:50
  • 1
    Anyhow, thinking that the If statement goes line by line and will stop when the comparision is true, what you need to know is that `Format(cell, "dddd") = "Saturday"` or sunday will do the trick, now use it with your logic. – Damian Sep 25 '19 at 08:52
  • Ok sorry, i didn't understand. If today is saturday then color as a today cell, so yellow. If today isn't Saturday/Sunday, color in RGB(170,170,170) – Lomoo Sep 25 '19 at 08:53
  • @Damian I didn't understand this logic well :( What should i do? – Lomoo Sep 25 '19 at 09:02
  • `If Format(cell, "dddd") = "Saturday" or Format(cell, "dddd") = "Sunday" Then` – Damian Sep 25 '19 at 09:03
  • And those "dddd" what do they mean? – Lomoo Sep 25 '19 at 09:04
  • 2
    @Lomoo go to Excel and cell formating, you can toy there and try what it does. Supose you have 25/09/2019, "dd" = 25, "ddd" = short name for the day, "dddd" = full name of the day. Same with month "mm", "mmm", "mmmm" you can format a date however you need – Damian Sep 25 '19 at 09:05