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

 

[ChrisOK]

No, in the example file I just uploaded, the code should do this:
1 - Part # (COL P) on BOM sheet (let's take "1222M49P01" as an example) - has a UPA/(qty) of "10" in COL E of the BOM sheet...
2 - it goes to find Part# match on TO sheet in COL B
3 - It finds a *MATCH* and brings the QTY of "50" found in COL G of the TO sheet over to the UPA/(qty) COL E on the BOM sheet. (overwrites it)
(this essentially happens when it pastes the formula into Col E of the BOM and the formula performs the looking up and summing action)

Scenario:
What if the Part # is found more than once on the TO?
If 1222M49P01 was found with a QTY of "50" and found again with a QTY of "20" then it would SUM the two together and paste "70" into COL E of the BOM sheet.
(this is where the formula is working: =SUMIF(TO!$B$8:$B$100,'BOM Worksheet'!P5,TO!$G$8:$G$100)

HOWEVER, THIS IS WHERE THE PROBLEM COMES IN......
Notice how Part # 5555M25P01 goes to the TO sheet to find a match...
--it finds a match but the QTY COL G on the TO reflects "REF" instead of an actual number that it can SUM...
It should bring over "REF" in this case and not try to SUM anything since it is obviously not 'sumable'...
NOTE: Everything in COL G is TEXT (even though some looks like numbers and some looks like text). (verified via =ISTEXT(xx) TRUE

* Last, any cells that return a "0" result when its all said and done should turn red in Col E of the BOM sheet.
* Other cells at the bottom of that column E of the BOM sheet that are blank should NOT be turning red... blanks should remain blank w/ no colorization...

Regarding the MESSAGE occuring twice: I corrected the message by commenting out the 2nd mssg and now it only shows if zeros are found in E after all the SUMMING is complete.

> I'm still trying to resolve the coloring of red issue (coloring blanks)
> ...and the issue of it not bringing over the code "REF" that I need to have brought over...

Hope that makes sense!?

 

[mumps]

Hi Chris. In your initial post, you had mentioned that you wanted to search column C instead of column E. I changed the macro to search column E in the BOM sheet. Blanks are not colored in red and I didn't comment out the second message and it works properly . Here is the revised macro:

Sub ChangeColor()
    Application.ScreenUpdating = False
    Dim lRow As Long
    lRow = Range("E" & Rows.Count).End(xlUp).Row
    Dim MR As Range
    Set MR = Range("E5:E" & lRow)
    Dim cel1 As Range
    Dim cel2 As Range
    If Application.WorksheetFunction.CountIf(Range("E5:E" & 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("E5:E" & 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]

Ok, thanks - I'm finding that when I run the abv code with the file that I uploaded as an example yesterday -- it works.
When I run it on one of my real files where data from a main frame system has fed data into a sheet - it does not work properly.
The difference? The small test file has hand-keyed data and the other file has imported data.

Now I've got to figure out how to get the code to appropriately clean it all --- so it will work on the real files. =CLEAN and =TRIM do not work -- I'm working on a char replacement option that will strip out extra chars that CLEAN and TRIM won't do..

*The other problem still exists on (both) files --- where --- when a code such as "AGH" or "REF" is present in the QTY Col G of the "TO" sheet, it does not feed the code over into Col E of the "BOM" sheet, it only feeds numbers over. (due to a limitation in the =SUM formula)
If you've got any ideas on how to resolve this last issue - input is greatly appreciated..

Thx again for the colorization and message correction help!
>Happy Friday<

 

[mumps]

Hi Chris. Could I suggest that it might be better to start a new thread for the "REF" problem since it's an entirely different question. Hopefully, you'll find a way to make the colorization code work for you.