How to show MsgBox if a any cell value is > 17 in a range

**syedalamgir** · 01-09-2011, 09:20 PM

Hi all

I've been working on 2 pieces of coding all weekend but seem to not get round it at all.

I'm trying to to show a message to the user when a value is >17 in any cell of a range (c7:g13).

I have the standard code:

if range ("c7") >17 then
MsgBox "one of the cells value is greater then 17" 
End if

i want it to look through my whole range (c7:g13) and if any cell has a value greater then 17 then show the message.

Can you help?

Also my second code i was looking for is similar, I'm doing a worksheet change event when columns 7 and 8 value = date and time on another sheet, to show an input box.

Please help, my hairs literally gone white thinking about this all weekend. I think i mjust being stupid and missing something obvious.

Thanks in advance.

**Palmetto** · 01-09-2011, 10:55 PM

If WorksheetFunction.CountIf(Range("C3:G13"), ">17") Then
    MsgBox "One of the cell values is greater than 17"
End If

**syedalamgir** · 01-10-2011, 12:10 AM

Hi Palmetto

Thanks for replying so quick but the code doesn't do anything, no errors etc and no messages come up when one of the cells in the range is greater then 17.

**davesexcel** · 01-10-2011, 01:42 AM

Try this

Sub Button1_Click()
    Dim rng As Range, c As Range
    Set rng = Range("C7:G13")
    For Each c In rng.Cells
        If c.Value > 17 Then MsgBox c.Address & " is greater then 17"
    Next c
End Sub

**Palmetto** · 01-10-2011, 07:26 AM

The code I gave you works just fine for me. How id you attempt to use it?

However, if you want notification for each individual cell in the range then davesexcel code will do just that. Just be aware that multiple message boxes will be displayed and have to be dealt with - could get annoying.

Also my second code i was looking for is similar, I'm doing a worksheet change event when columns 7 and 8 value = date and time on another sheet, to show an input box.

I suggest you post a sample workbook, or at least upload the code.

**syedalamgir** · 01-12-2011, 07:38 PM

Thanks Palmetto davesexcel code worked. but one other problem i am having with this is the range actually isn't c7:g13 its actual c7:g13 and then again from k7:o13 and then again from y7:ac13.

I've tried to insert the code as this

Sub Calculate_Worksheet()
    Dim rng As Range, c As Range
    Set rng = Range("c7:g13, k7:o13, y7:ac13")
     
    For Each c In rng.Cells
        If c.Value > 16 Then MsgBox " Too many students have been booked in please check the following Cell " & c.Address
    Next c
    
End sub

but this gives me a global error. can you help? I've added an attachment should you need to have a look at a sample.

**davesexcel** · 01-12-2011, 08:48 PM

If you are wanting the code to activate when the sheet calculates, then this needs to be placed in the worksheet module

Private Sub Worksheet_Calculate()

    Dim c As Range, rng As Range

    Set rng = Range("C7:G13,K7:O13,Y7:AC13,AG7:AK13")

    For Each c In rng.Cells

        If c > 16 Then MsgBox " Too many students have been booked in please check the following Cell " & c.Address

    Next c

End Sub

It will seem to take a long time for this to kick in as you have your formulas calculating 65536 rows.

Where the code goes