Jul 13 2021 04:36 AM
hi everyone, I have no experience really of macros (or how to create them) or conditional formatting so I'd be grateful for some pointers - not even sure what I need really!
I have a spreadsheet which has the customer 4-digit code in column A and depending on their payment terms I need them to be coloured one of 3 colours (orange, blue and pink depending on whether they pay at 30 days, 60 days or 90 days). This is a monthly task and can be time consuming to pick every one out and colour them individually.
How would I do this, I'm a complete dummy!
Thanks!
Jul 13 2021 05:42 AM
How and where is the payment term specified?
Perhaps you could attach a small anonymized sample workbook.
Jul 13 2021 06:08 AM
Jul 13 2021 06:25 AM
Thank you. See the attached version - now a .xlsm workbook, so you will have to allow macros when you open it. Click the button to colour the cells in column A.
Jul 13 2021 06:49 AM
Jul 13 2021 07:26 AM
SolutionPress Alt+F11 to activate the Visual Basic Editor.
You can see the code of the macro in Module1.
Clicking the button calls this macro.
It will still work if column A and/or column B have more data.
Jul 13 2021 09:48 AM
@CarolineH12 Okay, you seem to insist on a VBA solution, but you really don't need it. I wonder why you would colour code every customer first, rather than just give them a (number) code and then use Conditional Formatting to look up the code and to colour them. But that's your choice, of course. Excel is great with numbers but requires more complicated solutions if you want to work with colours. Attached an example that uses conditional formatting. Much easier, I believe, and no need for coding at all, and you can expand the ranges to suit your needs.
Jul 13 2021 07:26 AM
SolutionPress Alt+F11 to activate the Visual Basic Editor.
You can see the code of the macro in Module1.
Clicking the button calls this macro.
It will still work if column A and/or column B have more data.