If = to "0" display Message (only once)

[ChrisOK]

Trying to figure out how to incorporate a CONDITIONAL message appropriately.
If ANY CELLS in COL "x" were = "0" then display this message "xxx"
Else display this other message "xxx"...

If I simply insert the message under the conditional code section, it pops the message up after every row it loops through --- of course, I do NOT want that ---
*I need it to be smart enough to know if it found any zeros or not after looping through the full column and at the END -- display the appropriate message once!

Sub ChangeColorWMssg()

'TURNS THE FIRST VALUE = TO 0 GREEN
'TURNS THE 2ND VALUE GREATER THAN 0 RED
'The "cel.Resize(,1)" means that it will turn ONLY 1 CELL that color

    Application.ScreenUpdating = False
    Dim lRow As Long
    lRow = Range("C" & Rows.count).End(xlUp).Row
    Dim MR As Range
    Set MR = Range("C2:C" & lRow)
    Dim cel As Range
    For Each cel In MR
        If cel.Value = "0" Then
            cel.Resize(, 1).Interior.Color = RGB(0, 128, 0)
            MsgBox ("Zero values were discovered. Do not delete these, they'll be used during File Mtc.")

        ElseIf cel.Value > "0" Then
            cel.Resize(, 1).Interior.Color = RGB(255, 0, 0)
            MsgBox ("No Zero values were found. Proceed with your TO to BOM validation.")

        End If
        
    Next
    Application.ScreenUpdating = True
    
    
End Sub

 

[mumps]

Place this line:

Exit Sub

below this line:

MsgBox ("Zero values were discovered. Do not delete these, they'll be used during File Mtc.")

 

[ChrisOK]

I did exactly what you advised --- (only putting one "Exit Sub" following the 1st message)...
It corrected the issue with the 1st message (it now only displays it once and allows "OK" to be clicked once to end the message).
[HOWEVER[/b], it is still prompting multiple times with both the 1st and 2nd message popping up (each time it cycles through each cell in that row)...

When I attempted to put "Exit Sub" after both the 1st and 2nd messages, it does not work properly.
(It uses the 2nd message no matter what the values are -- and does not do any colorization as it previously did when a "0" value was present.

Sub ChangeColor()

    Application.ScreenUpdating = False
    Dim lRow As Long
    lRow = Range("C" & Rows.count).End(xlUp).Row
    Dim MR As Range
    Set MR = Range("C2:C" & lRow)
    Dim cel As Range
    For Each cel In MR
        If cel.Value = "0" Then
            cel.Resize(, 1).Interior.Color = RGB(255, 0, 0)
            MsgBox ("Zero values were discovered. Do not delete these, they'll be used during File Mtc")
            Exit Sub
            
            
        ElseIf cel.Value > "0" Then
            cel.Resize(, 1).Interior.Color = RGB(255, 255, 255)
            MsgBox ("No Zero values were found. Proceed with your TO to BOM validation.")

        End If
    Next
    Application.ScreenUpdating = True
  
End Sub

 

[mumps]

Hi Chris. See if this works for you.

Sub ChangeColor()
    Application.ScreenUpdating = False
    Dim lRow As Long
    lRow = Range("C" & Rows.Count).End(xlUp).Row
    Dim MR As Range
    Set MR = Range("C2:C" & lRow)
    Dim cel As Range
    If Application.WorksheetFunction.CountIf(Range("C2:C" & lRow), 0) >= 1 Then
        MsgBox ("Zero values were discovered. Do not delete these, they'll be used during File Mtc")
    End If
    For Each cel In MR
        If cel = 0 Then
            cel.Interior.Color = RGB(255, 0, 0)
        End If
    Next cel
    If Application.WorksheetFunction.CountIf(Range("C2:C" & lRow), 0) = 0 Then
        MsgBox ("No Zero values were found. Proceed with your TO to BOM validation.")
    End If
    For Each cel In MR
        If cel > 0 Then
            cel.Interior.Color = RGB(255, 255, 255)
        End If
    Next cel
    Application.ScreenUpdating = True
End Sub

 

[ChrisOK]

Hi Mumps: I'm getting a "Compile Error: For control variable already in use"
Looks like there's two lines that are identical:
"For Each cel In MR"
It is highlighting the second one...

 

[mumps]

Hi Chris. Try this one.

Sub ChangeColor()
    Application.ScreenUpdating = False
    Dim lRow As Long
    lRow = Range("C" & Rows.Count).End(xlUp).Row
    Dim MR As Range
    Set MR = Range("C2:C" & lRow)
    Dim cel1 As Range
    Dim cel2 As Range
    If Application.WorksheetFunction.CountIf(Range("C2:C" & lRow), 0) >= 1 Then
        MsgBox ("Zero values were discovered. Do not delete these, they'll be used during File Mtc")
    End If
    For Each cel1 In MR
        If cel1 = 0 Then
            cel1.Interior.Color = RGB(255, 0, 0)
        End If
    Next cel1
    If Application.WorksheetFunction.CountIf(Range("C2:C" & lRow), 0) = 0 Then
        MsgBox ("No Zero values were found. Proceed with your TO to BOM validation.")
    End If
    For Each cel2 In MR
        If cel2 > 0 Then
            cel2.Interior.Color = RGB(255, 255, 255)
        End If
    Next cel2
    Application.ScreenUpdating = True
End Sub

 

[ChrisOK]

1-When I tried this one, I made sure there were NO ZERO VALUES in the designated column of the destination sheet.
2-Ran the code and it it first popped up the 1st mssg: "ZERO VALUES WERE DISCOVERED",
3-when I clicked OK, the 2nd mssg popped up:.......... "NO ZERO values were found"
4-when I clicked OK again, it updated the designated column with a zero count found on the other sheet, (good)
5-turned that cell red that holds the zero (good)
6-but also turned all blank cells from row 16 down red (not good)...
*it should only be turning "0" value cells red and not do anything to blank cells below the range of the active data content...

2 Problems:
1 - it popped up both messages to the user...
the only mssg that should appear is "ZERO VALUES WERE DISCOVERED" (because it found a zero on the other sheet and is bring it over to this sheet to update it
I'm just going to comment out that 2nd message -- it appears this will solve this issue (and only alert user if zero is found)

2 - it turns blanks red when it should not

If #2 problem can be resolved, I think I'm ok with doing without the dual messages...

 

[mumps]

Hi Chris. I'm not sure what is happening. When I tried it on a dummy sheet, it worked fine for me. I think that it would be much easier to diagnose if I could see how your data is organized. Perhaps you could upload a copy of your file to a free site such as www.box.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[ChrisOK]

Here ya go - small test file with 2 tabs:
https://app.box.com/s/xmy53wpxvy5tbvsd61sh

 

[mumps]

Hi Chris. I ran the macro on both sheets in your file and it worked properly. The macro looks for any zeros in column C. Even if it finds only one zero, the warning message will pop up and it will highlight all the zeros in red. If there are no zeros, the second message pops up and any fill color in the cells is removed. Only one of the messages pops up depending on what it finds and it only appears one time. The only issue that I found is that if it finds zeros and there are also blank cells in column C, the blank cells will also be highlighted in red but if this is a problem it can be easily fixed. Does my description cover what you want to happen?