Conditional Formatting based on data in adjacent cell & restrict save without required data

[bjohnson]

In the range of (B23:L147). Col B has header "W/P/L/F", Col F has
header "Customer" and Col L has header "Amount". If the user enters
any data in any row in the range in column F, without entering data
into the adjacent columns B and L, I want the cells n that same row in
Col's B and L to highlight red. I also want to make sure they cannot
"save" the file without filling in those cells. Can anyone help me?

 

[stjori]

In the range of (B23:L147). Col B has header "W/P/L/F", Col F has
header "Customer" and Col L has header "Amount". If the user enters
any data in any row in the range in column F, without entering data
into the adjacent columns B and L, I want the cells n that same row in
Col's B and L to highlight red. I also want to make sure they cannot
"save" the file without filling in those cells. Can anyone help me?

Format > Conditional Formatting. In B23 and L23 and drag down: Formula
is:=AND(F23<>"",L23="",B23="") and add desired formatting.
To stop saving, add formula in M23 (or anywhere)
=IF(AND(F23<>"",B23="",L23=""),1,"") and drag down and then in
Workbook BeforeSave event,

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

If WorksheetFunction.Sum(Sheet1.Range("M23:M26")) > 0 Then
MsgBox "Please fill in gaps"
Cancel = True
End If

End Sub