William has attended:
Excel Advanced - Formulas & Functions course
Formatting using VBA
I'm looking to use VBA to change the formatting of a cell based on whether another cell is populated.
Specifically, cells in column G turn orange using conditional formatting when the date in them is older than 7 days. I now need them to turn "no fill" if there is anything (cell colour, dates, values) in cells H to J i.e. cell G2 should turn clear from orange if I put any value into I2. I need this to loop down until the first blank cell, normally about 200 rows down.
Please let me know if any clarification is needed.
RE: 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
