VBA - Highlight Mandatory Cells (certain columns if another column is not empty!)

[malibu1378]

Hello all,
I'm sorry if this has been asked before but I've only found part answers!

I'm a newbie and have never used VBA before and have basically figured out what I have got so far using various answers found on the net and trial and error!

Basically I have a sheet that is an expenses claim form.
Columns A - G (rows 2-28) should contain various information relating to their claim. One row per item claimed

I have got most of it to work how i'd like and have managed to get it to display a message box if the user tries to close the sheet when A-G and column K are empty.

However I would like to make it extra "fancy" and highlight the cells that are missing information if the value claimed is >£0.

I'm not sure of the "slickest" code to use but here is what I have (which is probably longer than I need! So if anyone can improve it, I'd be very grateful!!)

-------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set aa = Sheets("Travel Expenses Claim Form").Range("R9")
Set bb = Sheets("Travel Expenses Claim Form").Range("U9")
Set cc = Sheets("Travel Expenses Claim Form").Range("X9")
Set dd = Sheets("Travel Expenses Claim Form").Range("AA9")
Set ee = Sheets("Travel Expenses Claim Form").Range("AD9")
Set ff = Sheets("Travel Expenses Claim Form").Range("AG9")
Set gg = Sheets("Travel Expenses Claim Form").Range("AJ9")
Set hh = Sheets("Travel Expenses Claim Form").Range("AO9")


If _
(bb.Value = "No project number") Or (cc.Value = "No project name") Or (dd.Value = "No date entered") Or (ee.Value = "No Category Chosen") Or (ff.Value = "No description entered") Or (gg.Value = "Choose recharge") Or (hh.Value = "choose vat cat") Then
GoTo msgbox

End If

Call HideSheetForMacroCheck

Exit Sub

msgbox:
msgbox "For each claim please complete columns A to G and column K" _
, vbInformation + vbOKOnly, "Missing Information"

Cancel = True 'cancels the save event

---------------

The cell references R9, U9 etc are calcaltions that show the msg box if something is missing from columns A-G and K if there is something is in column L (a gbp or euro value). This works fine and the box appears each time.

What I'd ideally like to do is highlight any mandatory cells (columns A-G and K) on any given row that are empty if the matching cell in that row and in column L is >0. For example:
if any cell from L2:L28 is >0 then the corresponding cell in A-G and K is highlighted and must be filled in

I hope that make sense and is possible?!!

Thank you in advance!!
Paul

 

[Joe4]

Welcome to the Board!

What I'd ideally like to do is highlight any mandatory cells (columns A-G and K) on any given row that are empty if the matching cell in that row and in column L is >0. For example:
if any cell from L2:L28 is >0 then the corresponding cell in A-G and K is highlighted and must be filled in

I think you can do this with Conditional Formatting (take a look at it in Excel's built-in help file if you need more information on it).

Basically, I think you want to use the Formula option. Your formula for cell A2 would look like:

=AND($L2>0,A2="")

Then, choose the Fill format option with the color of your liking to highlight that cell when those conditions are met. You can then use the Format Painter to copy this Conditional Formatting to all the other cells you want to apply it to.

 

[malibu1378]

Thank you for your reply, I'll give that a go later.
I think I was trying to make it too complicated, and didn't even think of using conditional formatting!
Thanks again, it's greatly appreciated