Warning Message for non-zero check figures

DerpDerp24

New Member
Hi,

I have a databook with a large number of tabs that we use check figures to ensure consistency from tab to tab. These check figures are just a simple sum function that also subtracts from another tab's subtotal and when everything is working right, should sum to zero. For example, a check figure might look like:

=sum(A1:A10)-sum('Other tab'!A20)

Currently, we have those check figures in yellow throughout the workbook but the issue is that we have to be actively looking at the check figure in order for us to notice there is an issue. I don't believe data validation will work because it only works on input values-- not values resulting from a formula, but surely there is some sort of pop-up warning message I can create that will notify us when a check figure is now non-zero as we are tinkering with the databook?
 

jmacleary

Well-known Member
Hello and welcome. You could use conditional formatting of each check figure to change to red when its non-zero.
 

DerpDerp24

New Member
Hello and welcome. You could use conditional formatting of each check figure to change to red when its non-zero.
Thanks! Been a lurker for a while, but this question has finally gotten me in the door.

Yeah, I've considered conditional formatting as you described, but with how large each tab is in terms of columns and rows, it'd still be an issue of having our window in the right spot of the spreadsheet to be able to identify that the check figure is off with conditional formatting. That is why I'm hoping a warning pop-up is possible.
 

jmacleary

Well-known Member
OK. It would be possible to create some VBA to do what you want, but depending on how often the values go non-zero wouldnt a pop-up be annoying after a while? What about freezing the top row (if it isnt already), and use conditional formatting to change the colour of the row if any check goes non-zero? Without seeing an example of your sheet its difficult to be more specific.
 

sykes

Well-known Member
This needs to go into the Workbook module:
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "WS13" And Target.Address = "$D$3" And Target.Value <> 0 Then MsgBox "Sheet " & Sh.Name & " Checkcell " & Target.Address & " in error!"
End Sub
It'll fire each time a change is made anywhere in a workbook, but so quickly, you shouldn't notice - unless it finds an error, of course!!

You'll need to replace "WS13" with the sheet name where your check cell resides, and also "$D$3" with the reference to your actual check cell.
Also, you'll need to adapt it, adding each sheet's name which contains checkcells, and each checkcell's address.
If you only have a few sheets/checkcells, consider putting it into each sheet's code module instead, and adapt accordingly. That's keep down the number of times the code fires.
 

DerpDerp24

New Member
OK. It would be possible to create some VBA to do what you want, but depending on how often the values go non-zero wouldnt a pop-up be annoying after a while? What about freezing the top row (if it isnt already), and use conditional formatting to change the colour of the row if any check goes non-zero? Without seeing an example of your sheet its difficult to be more specific.
This worked! I appreciate yours and the other commenter's suggestions. If I were more familiar with VBA I may have opted for his solution but I think given the setup of my databook, a frozen row at the top with the auto checks is preferable. Thanks again everyone!
 

sykes

Well-known Member
Thanks for the feedback.
You can always give the VBA a go, if you fancy it, or if John's solution doesn't work out for you. Happy to talk you through it, if necessary.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top