Hi, thank you very much for your help, unfortunately it's not exactly what I needed
.
With this code it gives youe message after every input in column J straight away, without even giving a chance to put something in column K. I was thinking that it would be better if it could flag the missing inputs in column K on Saving the file.
Second thing is that after seeing the message you can still leave the cell in column K blank.
It's very close to the ideal solution and I would be very grateful if you could try to make it work as per my comments above.
Thank you
Kubabocz
OK whilst I couldn't seem to recreate your issue at this end, I have been able to change how this works more to your new requirements. The new macro is a Workbook_BeforeSave event instead of a Worksheet_Change event.
Firstly make another COPY of your workbook to test this out on.
Secondly you will need to go into the backend of your sheet and delete my original code.
Next, you will need to find your workbook on the left hand side in the VBA developer window, right-click on ThisWorkbook and select View Code. You can then copy and paste in the following code:
Rich (BB code):
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' Defines variables
Dim Cell, cRange As Range
' Defines LastRow as last row of column J with a value
LastRow = ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Row
' Defines range to check
Set cRange = Range("K2:K" & LastRow)
' For each cell in range
For Each Cell In cRange
' If column K is blank but columns J and L are not blank then...
If Cell.Value = "" And Cell.Offset(0, -1).Value <> "" And Cell.Offset(0, 1) <> "" Then
' Highlight the cell in column K with a yellow fill
Cell.Interior.ColorIndex = 6
' Else If columns J and K are not blank then...
ElseIf Cell.Value <> "" And Cell.Offset(0, -1).Value <> "" Then
' Remove the yellow fill from column K
Cell.Interior.ColorIndex = xlNone
End If
' Check next cell in range
Next Cell
' If the count of filled cells in column K is less than the count of filled cells in column L then...
If Application.WorksheetFunction.CountIf(Range("K2:K" & LastRow), "<>") < Application.WorksheetFunction.CountIf(Range("L2:L" & LastRow), "<>") Then
' Display an error message stating saving will not happen unless all highlighted cells must be filled in
MsgBox "Workbook will not be saved unless" & vbCrLf & _
"All highlighted cells have been filled in!"
' Cancel Save function
Cancel = True
End If
End Sub
A breakdown of what this new code is doing is as follows:
When you try and save the document...
- It checks the adjacent cells of any blank cells in column K. If K is blank but L and J are not (meaning K has been skipped), highlight the blank cell in column K yellow.
- If column J is filled in but columns K and L are blank, do nothing as technically K hasn't been skipped yet (L needs to be filled in for K to have been skipped).
- Throws out an error message that all highlighted cells must be completed or saving cannot happen.
- Cancels the Save function.
If however you then fill in the highlighted cells as required and then click Save again...
- It checks the adjacent cells of column K and if J, K and L are no longer blank (meaning K has NOT been skipped), remove the yellow highlight.
- Allows the Save function to complete
