LOOP controlling for date similarity

cpsnuggle

New Member
Joined
Jul 29, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Hey,

I want to control for date similarities, so End date needs to be the same or at max one bigger as Date. E.g. Date: 01.01, then the end date can either be 01.01 or 02.01

The columns start with B2 and C2, with the title as a part of it

End dateDate
01.0101.01
02.0101.01
01.0101.01
03.0101.01

I tried to make it below, but it doesn't colour B6 and C6, as it should.... Any suggestions?
The loop should be working until it hits a blank cell

VBA Code:
Sub Comparing()

Dim row_one As Integer
Dim row_two As Integer

row_one = 3
row_two = 3

Do While Range("B" & row_one).Value <> "" And Range("C" & row_two).Value <> ""
    If Range("B" & row_one).Value < Range("C" & row_two).Value Then
         Range("B" & row_one).Select
         With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 555
            .TintAndShade = 0
            .PatternTintAndShade = 0
            Range("J3").Select
        End With
Else
    Range("B" & row_one).Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If
row_one = row_one + 1
row_two = row_two + 1

Loop

End Sub

Thanks in advance
 
Last edited by a moderator:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Are your dates entered as Date or Text?
This would work much better if they are entered as Date.

Also, does this need to be VBA, as you should be able to do this using Conditional Formatting (no VBA required)?
 
Upvote 0
It's text (general). How would you make it work with Date?
Well it could be conditional formatting instead, but I would just like to incorporate it in some bigger VBA script - if that makes sense
Are your dates entered as Date or Text?
This would work much better if they are entered as Date.

Also, does this need to be VBA, as you should be able to do this using Conditional Formatting (no VBA required)?
 
Upvote 0
It's text (general)
Text and general are not necessarily the same thing. General can have numbers or text, while text can only have text.

Excel actually stores dates in Excel as numbers, specifically, the number of days since 1/0/1900. So dates really just are numbers with special formats.
So you can easily verify if an entry is a valid date by applying the ISNUMBER function to it, i.e.
Excel Formula:
=ISNUMBER(A2)
If that returns TRUE, it is a valid number/date. If it returns FALSE, it is text.

If they are text, you can easily convert them to dates using the "Text to Columns" functionality found under the "Data" menu.
- Just select the column with the values
- Then go to "Text to Columns"
- Hit "Next" until you get to step 3
- Choose the "Date" option
- If your entries show month first, then day, then choose the MDY date format option
- If your entries show day first, then month, then choose the DMY date format option
- Click Finish

This should convert all your entries to valid dates. If you want to show them like "01.01" again, then just change the Cell Format to Custom and enter "mm.dd" or "dd.mm", depending on whether you want to see the month or day first.

Now all your entries are dates, and you can use date math on them.
 
Upvote 0
Text and general are not necessarily the same thing. General can have numbers or text, while text can only have text.

Excel actually stores dates in Excel as numbers, specifically, the number of days since 1/0/1900. So dates really just are numbers with special formats.
So you can easily verify if an entry is a valid date by applying the ISNUMBER function to it, i.e.
Excel Formula:
=ISNUMBER(A2)
If that returns TRUE, it is a valid number/date. If it returns FALSE, it is text.

If they are text, you can easily convert them to dates using the "Text to Columns" functionality found under the "Data" menu.
- Just select the column with the values
- Then go to "Text to Columns"
- Hit "Next" until you get to step 3
- Choose the "Date" option
- If your entries show month first, then day, then choose the MDY date format option
- If your entries show day first, then month, then choose the DMY date format option
- Click Finish

This should convert all your entries to valid dates. If you want to show them like "01.01" again, then just change the Cell Format to Custom and enter "mm.dd" or "dd.mm", depending on whether you want to see the month or day first.

Now all your entries are dates, and you can use date math on them.
Didn't know you could convert them so easily - thanks a lot!
But regarding the math part. I need the function or VBA to go over each row to check if the two columns fulfil the restriction: Date = End Date or Date = End date + 1, and then colour the ones that don't fulfil it red and mark with a 1 in column K.
 
Upvote 0
But regarding the math part. I need the function or VBA to go over each row to check if the two columns fulfil the restriction: Date = End Date or Date = End date + 1, and then colour the ones that don't fulfil it red and mark with a 1 in column K.
Do you really need VBA?
The color part can be handled pretty easily with Conditional Formatting, and column K can be handled pretty easily with a formula.

I am not quite clear which date is supposed to be bigger (B or C) in your example, but if you want to color cells where the value in column C is greater than column B, then you would use a Conditional Formatting formula like (for row 2):
Excel Formula:
=AND($B2>0,$C2>0,$C2>$B2)
and choose your highlighting color.

To apply the Conditional Formatting to a whole range of cells are once, you just select the whole range you want to apply it to, then write the formula as it pertains to the very first cell in your selection (Excel is smart enough to adjust it for the others). It won't hurt to go too far, as the coloring is only applied if BOTH columns B and C are populated (so it will ignore any blank rows).

The formula in K2 would be similar:
Excel Formula:
=IF(AND(B2>0,C2>0,C2>B2),1,"")
 
Upvote 0
Do you really need VBA?
The color part can be handled pretty easily with Conditional Formatting, and column K can be handled pretty easily with a formula.

I am not quite clear which date is supposed to be bigger (B or C) in your example, but if you want to color cells where the value in column C is greater than column B, then you would use a Conditional Formatting formula like (for row 2):
Excel Formula:
=AND($B2>0,$C2>0,$C2>$B2)
and choose your highlighting color.

To apply the Conditional Formatting to a whole range of cells are once, you just select the whole range you want to apply it to, then write the formula as it pertains to the very first cell in your selection (Excel is smart enough to adjust it for the others). It won't hurt to go too far, as the coloring is only applied if BOTH columns B and C are populated (so it will ignore any blank rows).

The formula in K2 would be similar:
Excel Formula:
=IF(AND(B2>0,C2>0,C2>B2),1,"")
Yeah but with that IF statement, it will always return values that are bigger... The end date can only be one bigger so like one day after.
 
Upvote 0
Then ju
Yeah but with that IF statement, it will always return values that are bigger... The end date can only be one bigger so like one day after.
Then if it can only be equal to or one day bigger, just change it to:
Excel Formula:
=AND($B2>0,$C2>0,$C2<>$B2,$B2<>$C2+1)
and
Excel Formula:
=IF(AND(B2>0,C2>0,C2<>B2,B2<>C2+1),1,"")
 
Upvote 0
Solution
Then ju

Then if it can only be equal to or one day bigger, just change it to:
Excel Formula:
=AND($B2>0,$C2>0,$C2<>$B2,$B2<>$C2+1)
and
Excel Formula:
=IF(AND(B2>0,C2>0,C2<>B2,B2<>C2+1),1,"")
Thanks a lot!
Much appreciated
 
Upvote 0
You are welcome!
Glad I was able to help.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top