vendredi 1 novembre 2019

How to make .Cells.Value work as one of multiple actions in IF...THEN loop?

I am having a problem with .Cells.Value part of the following code:

   Dim lngLastRow As Long, lngRow As Long
   Dim strColumn As String
   Dim dateColumn As String

   strColumn = Split(Range("aaa").Address, "$")(1)
   dateColumn = Split(Range("bbb").Address, "$")(1)

   With ActiveSheet
      lngLastRow = .Cells(.Rows.Count, strColumn).End(xlUp).Row
      For lngRow = 2 To lngLastRow
         If IsDate(.Cells(lngRow, strColumn).Value) And .Cells(lngRow, strColumn).Value < .Cells(lngRow, dateColumn).Value + 5 Then
            .Rows(lngRow).Interior.Color = RGB(255, 128, 0)
         End If
         If IsDate(.Cells(lngRow, strColumn).Value) And .Cells(lngRow, strColumn).Value = .Cells(lngRow, dateColumn).Value Then
            .Rows(lngRow).Interior.Color = RGB(255, 0, 0)
            Cells(lngRow, 6).Value = "1" 'This line does not execute...
         End If
      Next lngRow
   End With

Cells(1, 7).Value = "2" 'This line is identical to the one in the loop, but executes as it should...

The desired result is that the If...Then function will compare date values of two columns and format the whole row accordingly. However, I also need to have number "1" entered in column 6 if the dates are equal.

I am confused, because the line seems to be working perfectly when it is not in the loop... I will appreciate any guidance. Thanks!

Aucun commentaire:

Enregistrer un commentaire