Coloring with VBA

[xsmurf]

Hello,

I have a question, because I have a problem regarding a VBA code

I found a code that does only partially what I want.
I'm trying to highlight dates that are equal or lower then the current date (this date is on a different sheet, called Setup and the date is in cell "F3")
If there is no date in the range given no formatting should take place.
And only the range given should be highlighted, and not the rows in between the range.

Can somebody assist me with this problem, it would be highly appreciated.

Sub ChangeColor()
    Dim myDate As Date
    Dim rngCell As Range
    Dim lrow  As Long
     'format the date excluding time
    myDate = FormatDateTime(Now, 2)

    For Each rngCell In Range("C3:BB3", "C27:BB27") 'The 2 ranges I want to be highlighted IF there is a date in it.

   Select Case DateDiff("d", FormatDateTime(rngCell.Value, 2), myDate)
        Case Is >= 0
        rngCell.Interior.ColorIndex = 44
        rngCell.Font.ColorIndex = 55
        Case Is = Empty
        rngCell.Interior.ColorIndex = xlNone

   End Select
    Next

    Exit Sub

End Sub

 

[Kenneth Hobson]

Normally, one would use condition formatting for that.

I would do it like:

Sub ChangeColor()
  Dim myDate As Date, rngCell As Range, lrow As Long
  Dim diff As Long
  
  'format the date excluding time
  myDate = FormatDateTime(Now, 2)
  
  'The 2 ranges I want to be highlighted IF there is a date in it.
  On Error Resume Next
  For Each rngCell In Range("C3:BB3", "C27:BB27")
    diff = FormatDateTime(rngCell.Value, 2) - myDate
    Select Case True
      Case diff >= 0 And IsDate(rngCell)
        rngCell.Interior.ColorIndex = 44
        rngCell.Font.ColorIndex = 55
      Case Else
        rngCell.Interior.ColorIndex = xlNone
    End Select
  Next rngCell
End Sub

 

[Peter_SSs]

I'm trying to highlight dates that are equal or lower then the current date (this date is on a different sheet, called Setup and the date is in cell "F3")

Do you really need a macro to do this?
Could you use Excel's built-in Conditional Formatting as follows?
1. Select from C3:BB3
2. Conditional Formatting -> New Rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true: =AND(C3<>"",C3<=Setup!$F$3) -> Format... -> apply the formatting you want -> Ok -> OK
3. With C3:BB3 still selected, click the Format Painter then click cell C27

BTW, if you do continue with the macro
myDate = Date would be simpler than myDate = FormatDateTime(Now, 2)

 

[xsmurf]

Thanks for the help.
I noticed that the code colored all the cells with a date in it within the range mentioned. However it should stop at the date of that day, and leave the other ones untouched.
So if it is Oct 10, the dates after Oct should not be colored, but the Oct 10 and before must be colored.
Hopefully you guys can help me with this.

In the main excel file that I'm using I do use conditional formatting, but because this excelfile has an option to make a new year, all the Info is automatically deleted and also some columns.
The conditional formatting doesn't always work properly after that.
A lot of people use the file too, and that also has it challenges.

That's why I want to switch to a macro too make sure that part always functions properly

 

[xsmurf]

The code doesn't look at the location where the date is mentioned, I guess I have too change the myDate?
How can I make it too look at the setup page on a specific cell?

Thanks for the help guys

 

[Peter_SSs]

How can I make it too look at the setup page on a specific cell?

Did you consider my suggestion which does look at the specific cell mentioned?

Could you use Excel's built-in Conditional Formatting as follows?
1. Select from C3:BB3
2. Conditional Formatting -> New Rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true: =AND(C3<>"",C3<=Setup!$F$3) -> Format... -> apply the formatting you want -> Ok -> OK
3. With C3:BB3 still selected, click the Format Painter then click cell C27

 

[Kenneth Hobson]

If the Case and Case Else do not suit, change it to suit. Maybe you need to reverse the Diff calculation or the equality operator >= to <=.

For the myDate deal:

Sub ChangeColor2()
  Dim myDate As Date, rngCell As Range, lrow As Long
  Dim diff As Long
  
  'format the date excluding time
  myDate = Worksheets("Setup").Range("F3").Value
  'myDate = FormatDateTime(Worksheets("Setup").Range("F3").Value, vbShortDate)
  If Not IsDate(myDate) Then Exit Sub
  
  'The 2 ranges I want to be highlighted IF there is a date in it.
  On Error Resume Next
  For Each rngCell In Range("C3:BB3", "C27:BB27")
    diff = FormatDateTime(rngCell.Value, vbShortDate) - myDate
    Select Case True
      Case diff >= 0 And IsDate(rngCell)
        rngCell.Interior.ColorIndex = 44
        rngCell.Font.ColorIndex = 55
      Case Else
        rngCell.Interior.ColorIndex = xlNone
    End Select
  Next rngCell
End Sub

 

[xsmurf]

thanks for your reply.

Tried your code, but it still doesn't work, it also doesn't matter if there is a date in a cell it will always color the row.
So it seems it doesn't use the date, it just colours the cells with a date in it. So not how it should work.

strange (at least for me )

 

[xsmurf]

@Peter_SSs
Thanks for the help
I tried the formula, but it doesn't do what I want. When I deleted row the formula range need to be altered too wat it was.
I do want to go for the VBA solution.

 

[Kenneth Hobson]

My code works exactly how I saw your problem. Maybe if you stated your goals, I could understand your needs better.

Most of these sorts of problems are based on some column value meeting some criteria. When that happens, they want the the whole used row's interior color changed. Some like the entire row which is "easier" to code but looks bad to me. Some like to make this automatic using a Sheet's Change event rather than a manually ran macro. Some want both.