excel pop up message

[chankish]

hello awesome people
i have an excel worksheet named Transit time who has integers as it counts days.
i want to have a pop up window in excel with a message like " you still have 7 days" or anything else if the transit time is less than 7 days per example , but i also want it to do two things :
- Color the cells who are less than 7 days
- Show the cells number in the message box


for now i have been able to set a small macro with "you still have 7 days" - changing the color (red per example )should be straightforward but i have something wrong in my writing ( BX is the column in excel)


please tell me what is wrong if possible .. and how to add the cells number ?!

Private Sub Workbook_Open()
    If BX <= 7 Then
        MsgBox "Adjustment is Below the Total"
        End If
For Each cell In BX
If cell.Value <= 7 Then
cell.Interior.ColorIndex = 3
cell.Font.ColorIndex = 2
cell.Font.Bold = True
End If
Next
End Sub

 

[Joe4]

Welcome to the Board!

First a few things:
1. You do not want to loop through all the rows on the entire worksheet (over 1 million rows). Limit it down to the last row with data in column BX. That will be much more efficient.
2. You do not designate which sheet this should happen on. If you have multiple sheets in your workbook, you will probably want a line to activate the sheet you want it to run on.
3. I don't know if you want the one message box for every row it finds meeting the criteria, or just one at the end if any rows meet the condition. I went with the later.

Here is what that code might look like:

Private Sub Workbook_Open()

    Dim lr As Long
    Dim cell As Range
    Dim ct As Integer
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column BX
    lr = Cells(Rows.Count, "BX").End(xlUp).Row
    
'   Loop through all rows
    For Each cell In Range("BX1:BX" & lr)
        If cell <= 7 Then
            cell.Interior.ColorIndex = 3
            cell.Font.ColorIndex = 2
            cell.Font.Bold = True
            ct = ct + 1
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
'   Return message if found any cells
    If ct > 0 Then MsgBox "Found some adjustments below the total"

End Sub

 

[Trevor G]

Welcome to the board. Rather than have a message box repeatedly indicate each cell containing the 7 and below, you can list the cell ids in the debug window. Example code below:

Private Sub Workbook_Open()
Sheets("Transit time").Select
Dim i%
For i = 1 To 125
If Range("BX" & i).Value <> "" Then
If Range("BX" & i).Value <= 7 Then
Range("BX" & i).Font.ColorIndex = 3
Debug.Print Cells(i, "BX").Address

End If
End If
Next i
End Sub

 

[Joe4]

For i = 1 To 125

How did you ascertain to only check exactly 125 rows?

 

[chankish]

hello Joe4

i get run time error 13 type mismatch when i run your code .
i will explain more : in the excel worksheet , i have a column containing numbers , these numbers might change every day so i am forced to check all the rows not just the last.
the check should be only in a worksheet named DATA in a column BX ( the first row is the title , so the range is BX:2 - BX:infinity )
the numbers in this column are integers only a they show days .. i have only full days like 1,2,40, etc
i want each time i open the excel file that the macros works by itself .
in my code i get the message :"adjustement is below the total" which i added in the message box , but i dont know which cells
i can show you an example of the data if u want
please advise

 

[chankish]

Welcome to the board. Rather than have a message box repeatedly indicate each cell containing the 7 and below, you can list the cell ids in the debug window. Example code below:

i get run time error 9 : subscript out of range when i do this even though the rows are more than 2000

 

[Trevor G]

Code was an example Joe.

 

[chankish]

Code was an example Joe.

sorry trevor , the name of the sheet was wrong .. it is DATA , the title of the column is Transit Time
i fixed it in the macro but now i am getting error 13 type mismatch

 

[Joe4]

these numbers might change every day so i am forced to check all the rows not just the last.

My code checks all rows with data, not just the last. It is just stopping at the last row of data instead of checking every possible row on your sheet, which will be slow and inefficient, as you would be checking over 1 million rows!

in my code i get the message :"adjustement is below the total" which i added in the message box , but i dont know which cells

Sorry, I thought that is why you were coloring the cells. Seems a bit redundant to both color them and tell the row numbers, but we can do that of you want.

Try this:

Private Sub Workbook_Open()

    Dim lr As Long
    Dim cell As Range
    Dim ct As Long
    Dim msg As String
    
    Application.ScreenUpdating = False
    
    Sheets("Data").Activate
    
    msg = "Adjustment is Below the Total in rows "
    
'   Find last row with data in column BX
    lr = Cells(Rows.Count, "BX").End(xlUp).Row
    
'   Loop through all rows
    For Each cell In Range("BX2:BX" & lr)
        If cell <= 7 Then
            cell.Interior.ColorIndex = 3
            cell.Font.ColorIndex = 2
            cell.Font.Bold = True
            ct = ct + 1
            msg = msg & cell.Row & ","
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
'   Return message if found any cells
    If ct > 0 Then MsgBox msg

End Sub

 

[chankish]

My code checks all rows with data, not just the last. It is just stopping at the last row of data instead of checking every possible row on your sheet, which will be slow and inefficient, as you would be checking over 1 million rows!


Sorry, I thought that is why you were coloring the cells. Seems a bit redundant to both color them and tell the row numbers, but we can do that of you want.

Try this:

Private Sub Workbook_Open()

    Dim lr As Long
    Dim cell As Range
    Dim ct As Long
    Dim msg As String
    
    Application.ScreenUpdating = False
    
    Sheets("Data").Activate
    
    msg = "Adjustment is Below the Total in rows "
    
'   Find last row with data in column BX
    lr = Cells(Rows.Count, "BX").End(xlUp).Row
    
'   Loop through all rows
    For Each cell In Range("BX2:BX" & lr)
        If cell <= 7 Then
            cell.Interior.ColorIndex = 3
            cell.Font.ColorIndex = 2
            cell.Font.Bold = True
            ct = ct + 1
            msg = msg & cell.Row & ","
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
'   Return message if found any cells
    If ct > 0 Then MsgBox msg

End Sub

i got type mismatch on this line : If cell <= 7 Then