Formatting using VBA

Hi William



Thank you for the forum question.

The combination of conditional formatting and VBA formatting is not a good solution. VBA formatting cannot override conditional formatting without first deleting the conditional formatting from the cell.

All the formatting part should be done using conditional formatting only or VBA formatting only.

Conditional formatting has some limitations. It can test if a cell is blank but not if the cell has formatting (colours).

You can create you own conditional formatting in the visual basic editor. I do not know how much experience you have with VBA, but you can create a worksheet change event for a specific range (Range("H2:J20000"). When something change in the specific range you can tell Excel what you need, but you will need the code knowledge.

If you want just to use the Conditional Formatting tool, you can create the two rules below in use a formula to determine which cells to format. My example data starts from row two. The rules will not test if there is a colour in range H:J

=G2<TODAY()-7

=OR(H2<>"", I2<>"", J2<>"")


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector