Forum Discussion
Playing with conditional formatting
This formatting is something I used to restructure the way I wrote formulas between 2015 and 2018. In the original I used VBA but now, as often happens, worksheet formulas used within conditional formatting are sufficient. The formula defining the condition is
= DIRECTREFλ(A1)
//Function to test for the presence of direct cell references
DIRECTREFλ
= LAMBDA(cell,
IF(ISFORMULA(cell), REGEXTEST(FORMULATEXT(cell), "\b[A-Z]{1,3}\${0,1}\d{1,6}\b"))
);The original was pretty effective as a training aid to avoid the practice of relative referencing and use defined names!
That said, not many followed my lead!
Mind you when I posted a discussion on Chandoo it didn't meet with overwhelming support, though subsequent events in the form of spilt ranges have made my life so much easier! The post did however stimulate more discussion than I had anticipated.
2 Replies
Hi Peter,
I’ve been reading through your topics and solutions, and I feel like my brain is exploding with newfound knowledge! It’s truly inspiring, and the discussions you’ve created make me want to dive deeper into the use of defined names, something I had rarely used before. So thanks for all your contributions!
Thank you for your approval. There are times when I feel I am broadcasting into a sea of incomprehension! Don't plough through it all but you may find some of the ideas from my EuSpRIG 2025 conference paper of interest.
